如何在SQL UNION中添加Order BY，总是会显示为最后一行？

Question

我有SQL UNION，该语句的第二部分是表示TOTALS的行。我该如何订购？TOTALS总是会显示为最后一行？

Answer 1

添加一个额外的列是被联合查询，使该列的第一列你的ORDER BY子句。

所以，如果我开始是这样的：

SELECT product, price 
FROM table 
UNION 
SELECT 'Total' AS product, SUM(price) 
FROM table 

我想补充一个新的列是这样的：

SELECT product, price 
FROM (
    SELECT product, price, 0 AS union_order 
    FROM table 
    UNION 
    SELECT 'Total' AS product, SUM(price), 1 AS union_order 
    FROM table 
) 
ORDER BY union_order 

这样一来，正规产品首先出现，那么总出现在结束。

Answer 2

你可以尝试添加一个“秩序”列每个查询，并通过该列，然后命令......

SELECT x.* 
FROM 
(
SELECT columns, 2 AS [Order] 
UNION 
SELECT totals, 1 AS [Order] 
) x 
ORDER BY x.[Order] 

Answer 3

select * from 
(
    select 1 as prio 
    , col1 
    , col2 
    ... 
    from tableA 
    union 
    select 2 as prio 
    , totalCol1 
    , totalCol2 
    ... 
    from tableB 
) order by prio 

Answer 4

您是否尝试过使用GROUP BY ROLLUP - 它可能只是想让你想要的，虽然这是很难说的时候你还没有发布查询。