sql-server - 使用UNION和ORDER BY选择TOP N

Question

给定以下示例，为什么在下面的查询2中将UNION ALL与两个SELECT TOP 5语句一起使用似乎不遵守ORDER BY子句？

查询1返回预期结果，但不包括所需的并集。
查询2演示了意外的行为。
查询3是我当前用于获得所需结果的解决方法。

CREATE TABLE #T1 ([ID] int IDENTITY(1,1), [Description] varchar(100), [Inactive] bit);
CREATE TABLE #T2 ([ID] int IDENTITY(1,1), [Description] varchar(100), [Inactive] bit);

INSERT INTO #T1([Description], [Inactive]) VALUES ('One', 1);
INSERT INTO #T1([Description], [Inactive]) VALUES ('Two', 0);
INSERT INTO #T1([Description], [Inactive]) VALUES ('Three', 1);
INSERT INTO #T1([Description], [Inactive]) VALUES ('Four', 0);
INSERT INTO #T1([Description], [Inactive]) VALUES ('Five', 1);
INSERT INTO #T1([Description], [Inactive]) VALUES ('Six', 0);
INSERT INTO #T1([Description], [Inactive]) VALUES ('Seven', 1);
INSERT INTO #T1([Description], [Inactive]) VALUES ('Eight', 1);
INSERT INTO #T1([Description], [Inactive]) VALUES ('Nine', 1);
INSERT INTO #T1([Description], [Inactive]) VALUES ('Ten', 0);

-- Query 1, works as expected giving all 4 records with Inactive = 0 plus one more
SELECT TOP 5 [ID], [Description], [Inactive]
FROM #T1
ORDER BY [Inactive], [Description]; 

-- Query 2, does not work as expected, as only 2 of the Inactive = 0 records are present
SELECT TOP 5 [ID], [Description], [Inactive]
FROM #T1
UNION ALL
SELECT TOP 5 [ID], [Description], [Inactive]
FROM #T2
ORDER BY [Inactive], [Description]; 

-- Query 3, Workaround to produce desired results
WITH T1 AS (
    SELECT TOP 5 [ID], [Description], [Inactive]
    FROM #T1
    ORDER BY [Inactive], [Description]
),
T2 AS (
    SELECT TOP 5 [ID], [Description], [Inactive]
    FROM #T2
    ORDER BY [Inactive], [Description]
)
SELECT [ID], [Description], [Inactive] FROM T1
UNION ALL
SELECT [ID], [Description], [Inactive] FROM T2
ORDER BY [Inactive], [Description]; 

DROP TABLE #T1;
DROP TABLE #T2;

显然，该变通方法对我来说是有效的，但我想了解为什么Query 2不能达到我的预期。如果您想知道为什么要打扰空表 #T2，结果实际上会受到生产示例中的 WHERE子句的限制-但是，在此处将其保留为空的目的是提供一个可比较的示例，而无需打扰它。

如果确实用以下内容填充 #T2，我发现结果也很奇怪-查询2仅给出 Inactive = 0的四个结果。

INSERT INTO #T2([Description], [Inactive]) VALUES ('Eleven', 1);
INSERT INTO #T2([Description], [Inactive]) VALUES ('Twelve', 0);
INSERT INTO #T2([Description], [Inactive]) VALUES ('Thirteen', 1);
INSERT INTO #T2([Description], [Inactive]) VALUES ('Fourteen', 0);
INSERT INTO #T2([Description], [Inactive]) VALUES ('Fifteen', 1);
INSERT INTO #T2([Description], [Inactive]) VALUES ('Sixteen', 0);
INSERT INTO #T2([Description], [Inactive]) VALUES ('Seventeen', 1);
INSERT INTO #T2([Description], [Inactive]) VALUES ('Eighteen', 1);
INSERT INTO #T2([Description], [Inactive]) VALUES ('Nineteen', 1);
INSERT INTO #T2([Description], [Inactive]) VALUES ('Twenty', 0);

我在SQL Server 2014和SQL Server 2008 R2上以相同的结果运行了该脚本。

Answer 1

联合查询的工作方式是:执行查询，然后应用order by子句。所以用

SELECT TOP 5 [ID], [Description], [Inactive]
FROM #T1
UNION ALL
SELECT TOP 5 [ID], [Description], [Inactive]
FROM #T2
ORDER BY [Inactive], [Description]; 

您可以从＃T1中选择5条任意选择的记录，再从＃T2中选择5条任意选择的记录，然后对它们进行排序。因此，您需要子查询或with子句。例如。:

SELECT * FROM
(
  (
    SELECT TOP 5 [ID], [Description], [Inactive]
    FROM #T1
    ORDER BY [Inactive], [Description]
  )
  UNION ALL
  (
    SELECT TOP 5 [ID], [Description], [Inactive]
    FROM #T2
    ORDER BY [Inactive], [Description]
  )
) t;

因此，您的解决方法根本不是解决方法，而是正确的查询。