sql - 每个要求ORDER BY子句的子查询的Tsql联合

嗨，我很抱歉，如果答案是其他地方。我环顾四周，看不到一个简单的解决方案。我有一个要运行的查询。我不在乎联合集的顺序，只是在内部查询返回正确的数据，即“最大区域”。

(SELECT TOP 5 * FROM [geo].[Areas] WHERE CountryID = @CountryID AND (TypeID = 'City')
ORDER BY [ShapeArea] DESC) as BiggestCities
    UNION ALL
(SELECT TOP 5 * FROM [geo].[Areas] WHERE CountryID =  @CountryID AND (TypeID = 'National park')
ORDER BY [ShapeArea] DESC) as BiggestParks

但是T-sql不会让我在子查询上有ORDER BY，而只能在整个联盟上。我真的希望不必创建临时表或类似的东西。

最佳答案

试试这个

SELECT *
  FROM (SELECT TOP 5 *
          FROM [geo].[Areas]
         WHERE CountryID = @CountryID
           AND (TypeID = 'City')
      ORDER BY [ShapeArea] DESC) as BiggestCities
UNION ALL
SELECT *
  FROM (SELECT TOP 5 *
          FROM [geo].[Areas]
         WHERE CountryID =  @CountryID
           AND (TypeID = 'National park')
      ORDER BY [ShapeArea] DESC) as BiggestParks

关于sql - 每个要求ORDER BY子句的子查询的Tsql联合，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/5159784/