SQL Server:ORDER BY 在带有 UNION 的子查询中 [英] SQL Server: ORDER BY in subquery with UNION

问题描述

我有两个查询与 UNION ALL¹ 结合:

--查询1选择口味、颜色来自朋友

 

--查询2选择口味，(选择前 1 种颜色来自彩虹哪里 Rainbows.StrangerID = Strangers.StrangerID按波长 DESC 排序) AS 颜色来自陌生人

当然，这两者单独工作都很好，但是当与 UNION ALL 结合使用时:

选择口味、颜色来自朋友联合所有选择口味，(选择前 1 种颜色来自彩虹哪里 Rainbows.StrangerID = Strangers.StrangerID按波长 DESC 排序) AS 颜色来自陌生人

查询失败并显示错误:

<块引用>

消息 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)走选择口味、颜色来自朋友联合所有选择口味，(选择前 1 种颜色来自彩虹哪里 Rainbows.StrangerID = Strangers.StrangerID按波长 DESC 排序) AS 颜色来自陌生人走DROP TABLE 彩虹DROP TABLE 陌生人DROP TABLE 朋友

<块引用>

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

脚注

• ¹人为的假设示例.或者不.

另见

• SQL 查询 - 在 UNION 中使用 Order By
• SQL UNION 和 ORDER BY
• SQL:在 UNION 查询中使用 Top 1订购方式
• 投影内相关子查询排序的影响

解决方案

有点小技巧，但这会奏效.

CREATE TABLE Friends (Flavor int, Color int)CREATE TABLE Strangers (Flavor int, StrangerID int)CREATE TABLE Rainbows (StrangerID int, Color int, Wavelength int)走选择口味、颜色来自朋友联合所有选择口味，(从(选择 TOP 1 颜色、波长来自彩虹哪里 Rainbows.StrangerID = Strangers.StrangerID按波长 DESC 排序) 作为富) AS 颜色来自陌生人走DROP TABLE 彩虹DROP TABLE 陌生人DROP TABLE 朋友

i have two queries being combined with a 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

Both of which, of course, work fine separately, but when combined with a 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

The query fails with the error:

Msg 104, Level 15, State 1, Line 3
ORDER BY items must appear in the select list if the statement contains a UNION operator.

How do i use an ORDER BY in a statement with a UNION ALL?

Copy-Pasteable Example

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

Server: Msg 104, Level 15, State 1, Line 2
ORDER BY items must appear in the select list if the statement contains a UNION operator.

Footnotes

• ¹Contrived hypothetical example. Or not.

See also

• SQL Query - Using Order By in UNION
• SQL UNION and ORDER BY
• SQL: Using Top 1 in UNION query with Order By
• Impact of ordering of correlated subqueries within a projection

解决方案

A bit of a hack, but this will work.

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

这篇关于SQL Server:ORDER BY 在带有 UNION 的子查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！