ОБЪЕДИНЕНИЕ ВСЕХ в CTE

Я пытаюсь заставить 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.


sql sql-server-2008 common-table-expression sql-server-2008-r2 union-all
person Echilon    schedule 24.01.2012    source источник
comment
Когда вы SELECT t1.SomeIntKey1 FROM Table1 t1 каким, по вашему мнению, будет номер строки? Вы его не указываете. Вы имели в виду объединение перед вычислением номера строки?   -  person ta.speot.is    schedule 24.01.2012
comment
Это имеет смысл и, кажется, работает без ROW_NUMBER. Где я должен поставить ROW_NUMBER, чтобы включить пейджинг?   -  person Echilon    schedule 24.01.2012

Ответы (2)


arrow_upward
5
arrow_downward

Вы должны убедиться, что оба набора результатов имеют одинаковые столбцы:

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 теперь всегда возвращает 0 для RowNum. Если вы хотите иметь столбец RowNum для результата UNION ALL, вам нужен еще один подзапрос:

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)
person Daniel Hilgarth    schedule 24.01.2012
comment
Спасибо. Проблема заключалась не в том, чтобы включить средний выбор. Выглядит противно, но работает. - person Echilon; 24.01.2012

arrow_upward
1
arrow_downward

Ваш второй SELECT в вашем UNION ALL не возвращает одинаковое количество столбцов, отсюда и проблема.

Пытаться:

;WITH Results_CTE AS (
  SELECT IntKey, ROW_NUMBER() OVER (ORDER BY IntKey) AS RowNum  
  FROM
  (
     SELECT t2.SomeIntKey2 AS IntKey
     FROM Table2 t2
        LEFT JOIN CalculatedData d ON d.Key = t1.SomeIntKey2
     WHERE Postcode LIKE 'CHX 1XX%' 
     UNION ALL 
     SELECT t1.SomeIntKey1 AS IntKey
     FROM Table1 t1
        LEFT JOIN CalculatedData d ON d.Key = t1.SomeIntKey1
     WHERE Postcode LIKE 'CHX 1XX%' 
  ) t
 ) 
SELECT * FROM Results_CTE a INNER JOIN CalclatedData d ON a.IntKey = d.Key
person AdaTheDev    schedule 24.01.2012