Upraszczanie i jak wykonać wiele CTE w zapytaniu


156

Mam to proste zapytanie T-SQL, które emituje kilka kolumn z tabeli, a także łączy informacje z innych powiązanych tabel.

Mój model danych jest prosty. Mam zaplanowane wydarzenie z uczestnikami. Muszę wiedzieć, ilu uczestników bierze udział w każdym wydarzeniu.

Moim rozwiązaniem jest dodanie CTE, który grupuje zaplanowane wydarzenia i zlicza liczbę uczestników.

Pozwoli mi to dołączyć do tych informacji na zaplanowane wydarzenie. Proste zapytanie.

Lubię, gdy moje zapytania są proste, jednak jeśli kiedykolwiek będę potrzebować dodatkowych tymczasowych wyników podczas mojego prostego zapytania, co mam zrobić?

Naprawdę chciałbym, gdybym mógł mieć wiele CTE, ale nie mogę, prawda? Jakie mam opcje tutaj?

Wykluczyłem widoki i robienie rzeczy w warstwie danych aplikacji. Wolę izolować zapytania SQL.

Odpowiedzi:


297

Możesz mieć wiele CTEs w jednym zapytaniu, a także użyć ponownie CTE:

WITH    cte1 AS
        (
        SELECT  1 AS id
        ),
        cte2 AS
        (
        SELECT  2 AS id
        )
SELECT  *
FROM    cte1
UNION ALL
SELECT  *
FROM    cte2
UNION ALL
SELECT  *
FROM    cte1

Należy jednak pamiętać, że SQL Servermoże ponownie ocenić CTEza każdym razem to jest dostępne, więc jeśli używasz wartości jak RAND(), NEWID()itd., Mogą zmieniać się między CTErozmowami.


3
To było takie proste. dokumentacja MSDN była nieco niejasna wokół problemu, nie mogłem znaleźć nic rozstrzygającego. Dziękuję Ci bardzo!
John Leidegren

1
Jest to udokumentowane w WITH common_table_expression (Transact-SQL) . Możesz zobaczyć to w sekcji składni (zwróć szczególną uwagę na [ ,...n ]in [ WITH <common_table_expression> [ ,...n ] ]. Przykład C, „Używanie wielu definicji CTE w jednym zapytaniu”, wyjaśnia to wyraźnie. Niestety, ten przykład nie jest dostarczany w dokumentacji SQL 2008 i starsze (tj. przykład nie został podany, gdy OP opublikował pytanie)
Brian

Mam podwojoną ilość rekordów na ten temat: /
Tom Stickel

@TomStickel spróbuj użyć tylko połowy zapytania, przed ostatniąUNION ALL
Quassnoi

@Quassnoi Tak, to zadziałało. Zrobiłem to po napisaniu komentarza. Nie jestem pewien, dlaczego ten drugi związek w ogóle istnieje ...
Tom Stickel

90

Z pewnością możesz mieć wiele CTE w jednym wyrażeniu zapytania. Wystarczy oddzielić je przecinkami. Oto przykład. W poniższym przykładzie istnieją dwa CTE. Jeden jest nazwany, CategoryAndNumberOfProductsa drugi nazwany ProductsOverTenDollars.

WITH CategoryAndNumberOfProducts (CategoryID, CategoryName, NumberOfProducts) AS
(
   SELECT
      CategoryID,
      CategoryName,
      (SELECT COUNT(1) FROM Products p
       WHERE p.CategoryID = c.CategoryID) as NumberOfProducts
   FROM Categories c
),

ProductsOverTenDollars (ProductID, CategoryID, ProductName, UnitPrice) AS
(
   SELECT
      ProductID,
      CategoryID,
      ProductName,
      UnitPrice
   FROM Products p
   WHERE UnitPrice > 10.0
)

SELECT c.CategoryName, c.NumberOfProducts,
      p.ProductName, p.UnitPrice
FROM ProductsOverTenDollars p
   INNER JOIN CategoryAndNumberOfProducts c ON
      p.CategoryID = c.CategoryID
ORDER BY ProductName

5
@JohnLeidegren: zamieszczenie poprawnej odpowiedzi w ciągu 2 minut od pierwszej poprawnej odpowiedzi zasługuje na pozytywną opinię, którą przynajmniej dałem.
Peter Majeed
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.