UNION操作符在SQLite中不返回任何结果

Question

我正在SQLite中工作，并试图根据各自的收入进行查询，以返回包含四个地区(沿海，高山，区域和地铁)中每个地区的前五个工作的表。针对特定区域的每个SELECT语句都会返回正确的结果，但是，使用UNION将这些单独的结果附加到一个表中不会返回任何带有红色下划线的UNION表:

-- 
select  job,
--          income as "Top_incomes",
1 as "income",
region.name
from customers
JOIN customer_region on customers.id = customer_region.customer_id
JOIN region on region.id = customer_region.region_id
where region.name =  "regional"
ORDER by income DESC limit 5;
UNION
select  job,
--          income as "Top_incomes",
2 as "income",
region.name
from customers
JOIN customer_region on customers.id = customer_region.customer_id
JOIN region on region.id = customer_region.region_id
where region.name = "coastal"
ORDER by income DESC limit 5

虽然每个语句的列数是3，并且每个语句都具有相同的列名，但我不确定为什么UNION不作为单个表返回任何结果。这里有什么错误的查询语法吗?由于

-- 
select  job,
--          income as "Top_incomes",
1 as "income",
region.name
from customers
JOIN customer_region on customers.id = customer_region.customer_id
JOIN region on region.id = customer_region.region_id
where region.name =  "regional"
ORDER by income DESC limit 5;
UNION
select  job,
--          income as "Top_incomes",
2 as "income",
region.name
from customers
JOIN customer_region on customers.id = customer_region.customer_id
JOIN region on region.id = customer_region.region_id
where region.name = "coastal"
ORDER by income DESC limit 5

我期望得到一个包含每个SELECT语句结果的表:来自"区域"地区的5个高收入工作，然后是来自"沿海"地区的5个高收入工作。我没有得到任何结果。

Answer 1

在联合查询中，最外层的ORDER BY子句应用于整个查询，而不是任何单独的选择。您可以使用以下语法:

SELECT *
FROM
(
SELECT job,
income AS Top_incomes,
1 AS income,
r.name
FROM customers c
INNER JOIN customer_region rc ON c.id = cr.customer_id
INNER JOIN region r ON r.id = cr.region_id
WHERE r.name = 'regional'
ORDER BY income DESC
LIMIT 5
)
UNION
SELECT *
FROM
(
SELECT job,
income,
2,
r.name
FROM customers c
INNER JOIN customer_region cr ON c.id = cr.customer_id
INNER JOIN region r ON r.id = cr.region_id
WHERE r.name = 'coastal'
ORDER BY income DESC
LIMIT 5
);