sql-server-2008 - 在包含 UNION 的报告中对 T-SQL 中的行进行排序

Question

我正在使用 SQL Server 2008-R2 和 Transact SQL。

我有一个状态报告，它从多个表/ View 返回数据并使用 UNION 将行“连接”在一起。

SQL 脚本的摘录是

    SELECT
    NR.FormStatus AS [Item],
    (SELECT COUNT(PerinatalReportID) FROM NotificationDatesForReport 
        WHERE FormStatus = NR.FormStatus AND NotificationDate BETWEEN dbo.DateNoTime(GetDate()) AND dbo.DateNoTime(GetDate() + 1)) AS [Today],
    (SELECT COUNT(PerinatalReportID) FROM NotificationDatesForReport WHERE FormStatus = NR.FormStatus) AS [Total]   

FROM
    NotificationDatesForReport AS NR

GROUP BY
    NR.FormStatus, NR.DisplayOrder  

UNION ALL

SELECT
    'Distinct password requests',
    (SELECT COUNT(DISTINCT ToEMail) FROM PasswordRequests
        WHERE DateProcessed BETWEEN dbo.DateNoTime(GetDate()) AND dbo.DateNoTime(GetDate() + 1)) AS [Today],
    (SELECT COUNT(DISTINCT ToEMail) FROM PasswordRequests) AS [Total]

这按预期工作，但是，带有 GROUP BY 子句的脚本第一部分中的项目顺序以随机顺序出现。我想做的是只对脚本的那部分进行排序，但我找不到一种方法来执行此操作，因为 SORT BY 子句必须在最后一个 UNION 之后。

欢迎所有建议。

Answer 1

您可以像这样将整个内容包装在 CTE 中...

WITH cte AS
(
 SELECT
    NR.FormStatus AS [Item],
    (SELECT COUNT(PerinatalReportID) FROM NotificationDatesForReport 
        WHERE FormStatus = NR.FormStatus AND NotificationDate BETWEEN dbo.DateNoTime(GetDate()) AND dbo.DateNoTime(GetDate() + 1)) AS [Today],
    (SELECT COUNT(PerinatalReportID) FROM NotificationDatesForReport WHERE FormStatus = NR.FormStatus) AS [Total],
    0 AS [Section], 
    ROW_NUMBER() OVER (ORDER BY NR.FormStatus, NR.DisplayOrder) AS [DisplayOrder]
FROM
    NotificationDatesForReport AS NR
GROUP BY
    NR.FormStatus, NR.DisplayOrder  

UNION ALL

SELECT
    'Distinct password requests',
    (SELECT COUNT(DISTINCT ToEMail) FROM PasswordRequests
        WHERE DateProcessed BETWEEN dbo.DateNoTime(GetDate()) AND dbo.DateNoTime(GetDate() + 1)) AS [Today],
    (SELECT COUNT(DISTINCT ToEMail) FROM PasswordRequests) AS [Total],
    1 AS [Section],
    ROW_NUMBER() OVER (ORDER BY ToEMail) AS [DisplayOrder]
) 

SELECT ***desired columns*** FROM cte ORDER BY Section, DisplayOrder