Блог

Главная - Вопросы по программированию - Несколько вариантов выбора в одном запросе, используя то же самое с

Несколько вариантов выбора в одном запросе, используя то же самое с

#sql #sql-server

Вопрос:

Этот запрос фильтрует некоторые идентификаторы из основной таблицы и возвращает все данные об этих идентификаторах из двух других таблиц в виде двух наборов записей:

 WITH filteredIds (fid) AS 
(
    SELECT id 
    FROM MasterTable 
    WHERE <somecondition>
)
SELECT * 
FROM Table1 
RIGHT JOIN filteredIds ON (Table1.id = filteredIds.fid);

WITH filteredIds (fid) AS 
(
    SELECT id 
    FROM MasterTable 
    WHERE <somecondition>
)
SELECT * 
FROM Table2 
RIGHT JOIN filteredIds ON (Table2.id = filteredIds.fid);

Это работает до сих пор, но было бы здорово иметь только одно предложение WITH, так как условие всегда одно и то же. Более того, условие часто записывается вручную, так как оно используется для сбора некоторых диагностических данных.

Но это

 WITH filteredIds (fid) AS 
(
    SELECT id 
    FROM MasterTable 
    WHERE <somecondition>
)
SELECT * 
FROM Table1 
RIGHT JOIN filteredIds ON (Table1.id = filteredIds.fid);

SELECT * 
FROM Table2 
RIGHT JOIN filteredIds ON (Table2.id = filteredIds.fid);

не работает, SQL Server утверждает, что он не знает объект filteredIds в последнем запросе.

Я что-то наблюдал, или это так не работает? Я предполагаю, что альтернативой будет временная таблица.

Комментарии:

1. CTE-это всего лишь часть другого отдельного оператора sql. Его определение не существует вне оператора, в котором оно определено и заполнено. В этом отношении это точно то же самое, что и производная таблица.

2. Если столбцы достаточно похожи, возможно, а union all ? Или как насчет встроенной функции с табличным значением, в основном параметризованного представления? Кстати, right join странно, нет ничего, с чем вы могли бы сделать то, с right чем вы не могли бы сделать left

Ответ №1:

Как только CTE был использован в запросе, он не может быть повторно использован в другом запросе. Насколько я знаю, прямого обходного пути нет. Возможно, самое близкое, чего вы хотите, — это создать настоящую временную таблицу:

 CREATE TABLE #temp (fid int);
INSERT INTO #temp (fid)
SELECT id FROM MasterTable WHERE <somecondition>;

Затем вы можете повторно использовать временную таблицу напрямую:

 SELECT * FROM Table1 a RIGHT JOIN #temp b ON a.id = b.fid;
SELECT * FROM Table2 a RIGHT JOIN #temp b ON a.id = b.fid;

Комментарии:

1. Вы немного преувеличиваете — у вас может быть несколько определений запросов CTE, но только одно предложение WITH.

2. @SMor Как только CTE был использован в запросе, он не может быть повторно использован в другом запросе. Позвольте мне сменить язык.

3. @TimBiegeleisen, мне VIEW кажется, что a ближе к CTE, чем временная таблица. По крайней мере, в SQL Server, потому что и представления, и CTE не материализуются и не встроены в основной запрос и оптимизированы как один большой запрос.

4. @Влад, я согласен… Я не думал о представлениях, когда писал свой ответ (который, кстати, все еще действителен).

Ответ №2:

Вы не можете делать с CTE то, что хотите. CTE не существует вне запроса, в котором он был определен.

Я думаю, что самое близкое к тому, что вы хотите, называется «вид».

 CREATE VIEW filteredIds
AS
SELECT id AS fid
FROM MasterTable 
WHERE <somecondition>
;

И теперь вы можете использовать это представление во многих других запросах.

 SELECT * 
FROM Table1 
RIGHT JOIN filteredIds ON (Table1.id = filteredIds.fid);

SELECT * 
FROM Table2 
RIGHT JOIN filteredIds ON (Table2.id = filteredIds.fid);

Кроме того, в SQL Server он будет работать точно так же, как CTE, в том смысле, что список отфильтрованных идентификаторов не материализуется, и механизм оптимизирует весь запрос, ссылающийся на представление. Он встроит определение представления во внешний / основной запрос, как если бы вы написали все в одном большом запросе. Точно так же, как это было бы с CTE.

С помощью временной таблицы вы явно записываете этот временный результат на диск и считываете его обратно.

В некоторых случаях это может быть лучше, в некоторых-нет.

Метки: ,