sql - CTE中的UNION ALL

Question

我正在尝试使UNION ALL与我用于分页的CTE一起使用。我需要从两个表中获取与一组条件匹配的所有记录，然后分页结果。第一张表的CTE如下所示：

;WITH Results_CTE AS (SELECT t1.SomeIntKey1, ROW_NUMBER() OVER (ORDER BY SomeIntKey1) AS RowNum  
 FROM Table1 t1
 LEFT JOIN CalculatedData d ON d.Key = t1.SomeIntKey1
 WHERE Postcode LIKE 'CHX 1XX%' 
 ) SELECT * FROM Results_CTE a INNER JOIN CalclatedData d ON a.SomeIntKey1 = d.Key WHERE RowNum >= 0 AND RowNum <= 10 OPTION(RECOMPILE) 

第二个表的分页SQL（工作正常）是：

;WITH Results_CTE AS (SELECT t2.SomeIntKey2, ROW_NUMBER() OVER (ORDER BY SomeIntKey2) AS RowNum  
 FROM Table2 t2
 LEFT JOIN CalculatedData d ON d.Key = t1.SomeIntKey2
 WHERE Postcode LIKE 'CHX 1XX%' 
 ) SELECT * FROM Results_CTE a INNER JOIN CalclatedData d ON a.SomeIntKey2 = d.Key WHERE RowNum >= 0 AND RowNum <= 10 OPTION(RECOMPILE) 

对于合并的分页数据，我尝试了类似的方法：

;WITH Results_CTE AS (SELECT t2.SomeIntKey2, ROW_NUMBER() OVER (ORDER BY SomeIntKey2) AS RowNum  
 FROM Table2 t2
 LEFT JOIN CalculatedData d ON d.Key = t1.SomeIntKey2
 WHERE Postcode LIKE 'CHX 1XX%' 
UNION ALL 
SELECT t1.SomeIntKey1
 FROM Table1 t1
 LEFT JOIN CalculatedData d ON d.Key = t1.SomeIntKey1
 WHERE Postcode LIKE 'CHX 1XX%' 
 ) SELECT * FROM Results_CTE a INNER JOIN CalclatedData d ON a.SomeIntKey2 = d.Key WHERE RowNum >= 0 AND RowNum <= 10 OPTION(RECOMPILE) 

但是，这会导致错误：
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

我知道UNION ALL在最佳情况下可能会造成混乱，尤其是对于连接，但是我实际上是从两个表中获取INT键的列表，然后将它们连接到包含我需要的数据的第三个键（两个表中的键都会出现在 Data表的连接列中。

Answer 1

您需要确保两个结果集具有相同的列：

WITH Results_CTE AS
(
    SELECT
        t2.SomeIntKey2 as Key,
        ROW_NUMBER() OVER (ORDER BY SomeIntKey2) AS RowNum  
    FROM
        Table2 t2
    LEFT JOIN CalculatedData d
        ON  d.Key = t1.SomeIntKey2
    WHERE Postcode LIKE 'CHX 1XX%' 
    UNION ALL 
    SELECT
        t1.SomeIntKey1 as Key,
        0 as RowNum
    FROM
        Table1 t1
    LEFT JOIN CalculatedData d
        ON  d.Key = t1.SomeIntKey1
    WHERE Postcode LIKE 'CHX 1XX%' 
 )
 SELECT * FROM Results_CTE a INNER JOIN CalclatedData d ON a.SomeIntKey2 = d.Key
 WHERE RowNum >= 0 AND RowNum <= 10 OPTION(RECOMPILE) 

请注意， UNION ALL的第二部分现在始终为RowNum返回0。如果要在 UNION ALL的结果中包含RowNum列，则需要另一个子查询：

WITH Results_CTE AS
(
    SELECT
        s.Key,
        ROW_NUMBER() OVER (ORDER BY s.Key) AS RowNum
    FROM
    (
        SELECT
            t2.SomeIntKey2 as Key,
        FROM
            Table2 t2
            LEFT JOIN CalculatedData d
                ON  d.Key = t1.SomeIntKey2
        WHERE Postcode LIKE 'CHX 1XX%' 
        UNION ALL 
        SELECT
            t1.SomeIntKey1 as Key
        FROM
            Table1 t1
            LEFT JOIN CalculatedData d
                ON  d.Key = t1.SomeIntKey1
        WHERE Postcode LIKE 'CHX 1XX%' 
     )
 )
 SELECT * FROM Results_CTE a INNER JOIN CalclatedData d ON a.SomeIntKey2 = d.Key
 WHERE RowNum >= 0 AND RowNum <= 10 OPTION(RECOMPILE)