SQL UNION with ORDER

Question

这句话有什么问题？我得到以下错误：

Msg 156，Level 15，State 1，Line 4关键字'ORDER‘附近的语法不正确。

Msg 156，Level 15，State 1，Line 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只能有一个order by，该order by只能在最后一个select中。一个子查询只能有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

除非将SELECT TOP指定为/EDIT，否则ORDER BY在视图编辑中无效(仅在SQLServer中)。尝试在连接表之后对结果集进行排序。