sql - 公用表表达式中按问题排序

Question

我在下面显示的存储过程中使用通用表表达式我想按升序显示国家/地区名称

1   | India2   | Bangladesh3   | Srilanka4   | Nepal5   | Japan

The expression is as follows

WITH CTECountry as
(
SELECT 
   CountryID,
   CountryName 
FROM dbo.Country
ORDER BY CountryName ASC        
)
SELECT 
*
FROM CTECountry 
UNION SELECT '0','--Select--'

Answer 1

ORDER BY 适用于整个 UNION(在这种情况下需要 ALL)。

无论如何，只有最外层的 ORDER BY 子句保证对结果进行排序。除某些情况外，内部和中间 ORDER BY 子句不会产生任何影响或产生错误。

WITH CTECountry as
(
SELECT 
   CountryID,
   CountryName 
FROM dbo.Country
)  
SELECT 
   CountryID,
   CountryName 
FROM CTECountry 
UNION ALL
SELECT '0','--Select--'
ORDER BY CountryName ASC   -- goes here

编辑，我怀疑你在上面评论后想要这个

WITH CTECountry as
(
SELECT 
   CountryID,
   CountryName,
   1 AS SortOrder
FROM dbo.Country
UNION ALL
SELECT '0','--Select--', 2
)  
SELECT 
   CountryID,
   CountryName 
FROM 
   CTECountry
ORDER BY
   SortOrder, CountryName ASC