代码之家 › 专栏 › 技术社区 › Ian Boyd

SQL Server:具有UNION的子查询中的ORDER BY

union-all union sql-order-by sql-server-2000 sql-server

Ian Boyd · 技术社区 · 14 年前

我有两个查询与一个 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

当然,这两种方法分开使用效果都很好,但如果与 联合所有人 :

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项必须出现在select列表中。

如何在带有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项必须出现在select列表中。

脚注

¹ 假想的例子。不管怎样。

3 回复 | 直到 7 年前

Joe Stefanelli 14 年前

有点老套,但这会管用的。

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

Brett 14 年前

我知道您可以使用CTE(commontableexpression),在这里您可以使用orderby来表示CTE。

;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)中返回orderby列。

让我知道它是怎么工作的。

Matt Gibson 14 年前

实际上,看看我评论的链接中的解决方法,您可能想尝试以下方法:

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个盒子了,不管是虚拟的还是其他的。

编辑:啊!看起来乔和我在偷偷摸摸的问题上重叠了:)

Gil Allen 4 年前

为每个源表运行insert查询到变量表要加入的表,包括所需的所有筛选器和排序申请。
返回变量表

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