Wierd语法错误使用Order By子句与Union All中的Toad for Sql Server 2016

Question

我希望联合使用两个结果集的句点。第一组包含工资关闭期间，第二组只有一个活动期间。所以我在第二组中按照从小到大的顺序排序。但我总是得到“接近联合关键字的错误语法”。我无法追踪这发生了什么。

SELECT SysId AS PeriodId, 
     [Name] AS PeriodName, 
     Number AS PeriodNumber, 
     [Year] AS PeriodYear, 
     PayrollSetId AS PayrollSetId 
    FROM (SELECT p.SysId AS PeriodId, 
       p.[Name] AS PeriodName, 
       p.Number AS PeriodNumber, 
       p.[Year] AS PeriodYear, 
       p.PayrollSetId AS PayrollSetId 
      FROM PeriodPayrollSetLookupView p 
     WHERE p.Closed = 1 
     UNION ALL 
     SELECT TOP 1 
       p.SysId AS PeriodId, 
       p.[Name] AS PeriodName, 
       p.Number AS PeriodNumber, 
       p.[Year] AS PeriodYear, 
       p.PayrollSetId AS PayrollSetId 
      FROM PeriodPayrollSetLookupView p 
     WHERE isnull (p.Closed, 0) = 0 
     ORDER BY p.[Year], p.[Name] 
     UNION ALL 
     SELECT '00000000-0000-0000-0000-000000000000' AS PeriodId, 
     '<Select...>' AS PeriodName, 
     NULL AS PeriodNumber, 
     NULL AS PeriodYear, 
     '00000000-0000-0000-0000-000000000000' AS PayrollSetId)AS PERIOD 
ORDER BY [Year] DESC, Number DESC 

非常感谢您的帮助。

Answer 1

您不能在UNION中使用的选择中使用ORDER BY。您可以使用CTE's来重写它：

WITH closed AS (SELECT p.SysId AS periodid, 
        p.[Name] AS periodname, 
        p.Number AS periodnumber, 
        p.[year] AS periodyear, 
        p.PayrollSetId AS payrollsetid 
       FROM PeriodPayrollSetLookupView p 
      WHERE p.closed = 1), 
active AS (SELECT TOP 1 
        p.SysId AS periodid, 
        p.[Name] AS periodname, 
        p.Number AS periodnumber, 
        p.[year] AS periodyear, 
        p.PayrollSetId AS payrollsetid 
       FROM PeriodPayrollSetLookupView p 
      WHERE isnull (p.closed, 0) = 0 
      ORDER BY p.[year], p.[name]), 
x AS (SELECT '00000000-0000-0000-0000-000000000000' AS periodid, 
       '<select...>' AS periodname, 
       NULL AS periodnumber, 
       NULL AS periodyear, 
       '00000000-0000-0000-0000-000000000000' AS payrollsetid) 
SELECT * 
    FROM (SELECT periodid, 
       periodname, 
       periodnumber, 
       periodyear, 
       payrollsetid 
      FROM closed 
     UNION ALL 
     SELECT periodid, 
       periodname, 
       periodnumber, 
       periodyear, 
       payrollsetid 
      FROM active 
     UNION ALL 
     SELECT periodid, 
       periodname, 
       periodnumber, 
       periodyear, 
       payrollsetid 
      FROM x) t 
ORDER BY periodyear DESC, periodnumber DESC