sql server语言 - SQL UNION with ORDER

Question

这种说法有什么问题？我收到以下错误：

Msg 156，级别 15，状态 1，第 4 行关键字"ORDER"附近的语法不正确。

Msg 156，级别 15，状态 1，第 9 行关键字"ORDER"附近的语法不正确。

(SELECT TOP 1 dbo.ZERORATES.Maturity_Date AS Date1, dbo.ZERORATES.Zero_Rate AS Rate1
    FROM dbo.ZERORATES
    WHERE dbo.ZERORATES.Maturity_Date < '2013-05-16'
    ORDER BY dbo.ZERORATES.Maturity_Date DESC)
UNION
(SELECT TOP 1 dbo.ZERORATES.Maturity_Date AS Date2, dbo.ZERORATES.Zero_Rate AS Rate2
    FROM dbo.ZERORATES
    WHERE dbo.ZERORATES.Maturity_Date > '2013-05-16'
    ORDER BY dbo.ZERORATES.Maturity_Date ASC)

Answer 1

尝试将查询嵌入到 select 中

select * from
(SELECT TOP 1 dbo.ZERORATES.Maturity_Date AS Date1, dbo.ZERORATES.Zero_Rate AS Rate1
    FROM dbo.ZERORATES
    WHERE dbo.ZERORATES.Maturity_Date < '2013-05-16'
    ORDER BY dbo.ZERORATES.Maturity_Date DESC) as T
UNION
select * from
(SELECT TOP 1 dbo.ZERORATES.Maturity_Date AS Date2, dbo.ZERORATES.Zero_Rate AS Rate2
    FROM dbo.ZERORATES
    WHERE dbo.ZERORATES.Maturity_Date > '2013-05-16'
    ORDER BY dbo.ZERORATES.Maturity_Date ASC) as T

Answer 2

一个 UNION 只能有一个顺序，该顺序应该只在最后一个选择中 此外，一个子查询只能有 1 列，将其更改为以下内容：

SELECT TOP 1 dbo.ZERORATES.Maturity_Date AS Date1, dbo.ZERORATES.Zero_Rate AS Rate1
    INTO #Temp1
FROM dbo.ZERORATES    
WHERE dbo.ZERORATES.Maturity_Date < '2013-05-16'  
ORDER BY dbo.ZERORATES.Maturity_Date DESC
UNION
SELECT TOP 1 dbo.ZERORATES.Maturity_Date AS Date1, dbo.ZERORATES.Zero_Rate AS Rate1
    INTO #Temp2    
FROM dbo.ZERORATES    
WHERE dbo.ZERORATES.Maturity_Date > '2013-05-16'    
ORDER BY dbo.ZERORATES.Maturity_Date ASC
SELECT * FROM #Temp1
UNION 
SELECT * FROM #Temp2

Answer 3

ORDER BY 在视图 EDIT 中无效（仅在 SQLServer 中），除非指定了 SELECT TOP。 尝试在联接表后对结果集进行排序。

Answer 4

UNION 只能有一个 ORDER，要获得顶部和底部值，您可以使用 MIN 和 MAX

SELECT MAX(dbo.ZERORATES.Maturity_Date) AS Date, dbo.ZERORATES.Zero_Rate AS Rate
FROM dbo.ZERORATES
WHERE dbo.ZERORATES.Maturity_Date < '2013-05-16'
GROUP BY dbo.ZERORATES.Zero_Rate
UNION
SELECT MIN(dbo.ZERORATES.Maturity_Date) AS Date, dbo.ZERORATES.Zero_Rate AS Rate
FROM dbo.ZERORATES
WHERE dbo.ZERORATES.Maturity_Date > '2013-05-16'
GROUP BY dbo.ZERORATES.Zero_Rate

Answer 5

如果每个日期不超过 1 行，这将可以：

SELECT z.Maturity_Date AS Date
     , z.Zero_Rate AS Rate
FROM
    dbo.ZERORATES z
        JOIN 
        (   SELECT MAX(dbo.ZERORATES.Maturity_Date) AS D
            FROM dbo.ZERORATES
            WHERE dbo.ZERORATES.Maturity_Date < '2013-05-16'
        UNION  
            SELECT MIN(dbo.ZERORATES.Maturity_Date) AS D
            FROM dbo.ZERORATES
            WHERE dbo.ZERORATES.Maturity_Date > '2013-05-16'
        )
    AS maxmin
        ON z.Maturity_Date = maxmin.D

我确信有一个使用OVER()和PARTITION BY的更优雅的解决方案，但我现在不在 SQL 服务器附近进行检查。