SQL Server：子查询中的 ORDER BY 与 UNION

Question

我有两个查询与 UNION ALL¹ 组合在一起：

--Query 1
SELECT Flavor, Color
FROM Friends

 

--Query 2
SELECT Flavor,
    (SELECT TOP 1 Color
     FROM Rainbows
     WHERE Rainbows.StrangerID = Strangers.StrangerID
     ORDER BY Wavelength DESC
    ) AS Color
FROM Strangers

当然，这两者单独工作都可以正常工作，但是当与 UNION ALL 结合使用时：

SELECT Flavor, Color
FROM Friends

UNION ALL

SELECT Flavor,
    (SELECT TOP 1 Color
     FROM Rainbows
     WHERE Rainbows.StrangerID = Strangers.StrangerID
     ORDER BY Wavelength DESC
    ) AS Color
FROM Strangers

查询失败并出现错误：

消息 104，级别 15，状态 1，第 3 行
如果语句包含 UNION 运算符，ORDER BY 项必须出现在选择列表中。

如何在带有 UNION ALL 的语句中使用 ORDER BY？

可复制粘贴示例

CREATE TABLE Friends (Flavor int, Color int)
CREATE TABLE Strangers (Flavor int, StrangerID int)
CREATE TABLE Rainbows (StrangerID int, Color int, Wavelength int)
go

SELECT Flavor, Color
FROM Friends

UNION ALL

SELECT Flavor,
    (SELECT TOP 1 Color
     FROM Rainbows
     WHERE Rainbows.StrangerID = Strangers.StrangerID
     ORDER BY Wavelength DESC
    ) AS Color
FROM Strangers
go

DROP TABLE Rainbows
DROP TABLE Strangers
DROP TABLE Friends

服务器：消息 104，级别 15，状态 1，第 2 行
如果语句包含 UNION 运算符，ORDER BY 项必须出现在选择列表中。

脚注

• ¹人为的假设示例。或不。

另请参阅

• SQL 查询 - 在 UNION 中使用 Order By
• SQL UNION 和 ORDER BY
• SQL：在带有 Order By 的 UNION 查询中使用 Top 1
• 投影内相关子查询排序的影响

Answer 1

有点黑客，但这会起作用。

CREATE TABLE Friends (Flavor int, Color int)
CREATE TABLE Strangers (Flavor int, StrangerID int)
CREATE TABLE Rainbows (StrangerID int, Color int, Wavelength int)
go

SELECT Flavor, Color
FROM Friends

UNION ALL

SELECT Flavor,
    (SELECT Color FROM 
        (SELECT TOP 1 Color, Wavelength
         FROM Rainbows
         WHERE Rainbows.StrangerID = Strangers.StrangerID
         ORDER BY Wavelength DESC
         ) AS Foo
    ) AS Color
FROM Strangers
go

DROP TABLE Rainbows
DROP TABLE Strangers
DROP TABLE Friends

Answer 2

我知道您可以使用 CTE（通用表表达式），您可以在其中使用 CTE 的 order by 。

;with results as
(

    SELECT Cassettes.CassetteNumber,
    LastCassetteTransfers.Amount,
    CassetteTransfers.CreatedDate
    FROM Cassettes
    INNER JOIN LastCassetteTransfers
    ON Cassettes.CassetteGUID = LastCassetteTransfers.CassetteGUID

    UNION ALL

    SELECT Cassettes.CassetteNumber,
    (
       SELECT TOP 1 CassetteTransfers.Amount
       FROM CassetteTransfers
       WHERE CassetteTransfers.CassetteGUID = Cassettes.CassetteGUID
       AND CassetteTransfers.Mode = 'ctmLoad'
    ) AS Amount,
    CassetteTransfers.CreatedDate
    FROM Cassettes

)

SELECT CassetNumber, Amount
FROM results
ORDER BY CassetteTransfers.CreatedDate DESC, CassetteTransfers.Amount

那应该有帮助。重要的是确保您按内部查询（在本例中为 CTE）中返回的列进行排序。

让我知道它是如何工作的。

Answer 3

实际上，看看我评论的链接中的解决方法，您可能想尝试这个：

SELECT Flavor, Color
FROM Friends

UNION ALL

SELECT Flavor,
(SELECT TOP 1 Color FROM 
    (SELECT Color, Wavelength
    FROM Rainbows
    WHERE Rainbows.StrangerID = Strangers.StrangerID
) X ORDER BY Wavelength DESC) AS Color
FROM Strangers

...或一些类似类型的东西来试图欺骗引擎不抱怨。

但恐怕我无法测试它；我认为我们大楼里已经没有 2000 个盒子了，无论是虚拟的还是其他的。

编辑：啊！看起来乔和我在偷偷摸摸上有重叠:)

Answer 4

我建议以您想要的列的格式创建一个变量表。

1. 为每个变量运行从原始表到变量表的插入查询
   您要加入的表格，包括您想要的所有过滤器和排序
   申请。
2. 返回变量表示

例：

set nocount on
DECLARE @temp_table TABLE(Flavor varchar(20), Color varchar(20))
    
    insert into @temp_table (Flavor,Color)
    /*Apply select query #1 with all filters, joins and sorting */
    SELECT Flavor,Color   FROM Strangers  ORDER BY Wavelength DESC
    
    insert into @temp_table (Flavor,Color)
    /*Apply select query #2 with all filters, joins and sorting */
    SELECT Flavor, Color FROM Friends
    
    /*Return the results pushed into @variable table */
    select * from @temp_table