UNION运算符在SQLite中不返回任何结果

iih3973s 于 2022-12-13 发布在 SQLite

关注(0)|答案(1)|浏览(202)

我正在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个来自”沿海“地区的高收入工作。我没有得到任何结果。

sqlite

来源：https://stackoverflow.com/questions/74772517/union-operator-does-not-return-any-results-in-sqlite

1条答案

按热度按时间

gpfsuwkq1#

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

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
);

赞(0）回复(0）举报 2022-12-13

我来回答

UNION运算符在SQLite中不返回任何结果

1条答案

相关问题

热门标签

最新问答