SQL
Allgemeine Tabellenausdrücke

Syntax

• WITH QueryName [(Spaltenname, ...)] AS (
  WÄHLEN ...
  )
  SELECT ... FROM QueryName ...;

• WITH RECURSIVE QueryName [(Spaltenname, ...)] AS (
  WÄHLEN ...
  UNION [ALL]
  SELECT ... FROM QueryName ...
  )
  SELECT ... FROM QueryName ...;

Bemerkungen

Temporäre Abfrage

Diese verhalten sich wie verschachtelte Unterabfragen, jedoch mit einer anderen Syntax.

WITH ReadyCars AS (
  SELECT *
  FROM Cars
  WHERE Status = 'READY'
)
SELECT ID, Model, TotalCost
FROM ReadyCars
ORDER BY TotalCost;

Äquivalente Unterabfragesyntax

SELECT ID, Model, TotalCost
FROM (
  SELECT *
  FROM Cars
  WHERE Status = 'READY'
) AS ReadyCars
ORDER BY TotalCost

rekursiv in einem Baum aufsteigen

WITH RECURSIVE ManagersOfJonathon AS (
    -- start with this row
    SELECT *
    FROM Employees
    WHERE ID = 4

    UNION ALL

    -- get manager(s) of all previously selected rows
    SELECT Employees.*
    FROM Employees
    JOIN ManagersOfJonathon
        ON Employees.ID = ManagersOfJonathon.ManagerID
)
SELECT * FROM ManagersOfJonathon;

Werte generieren

Die meisten Datenbanken verfügen nicht über eine native Methode zum Generieren einer Reihe von Zahlen für Ad-hoc-Zwecke. Bei der Rekursion können jedoch übliche Tabellenausdrücke verwendet werden, um diesen Funktionstyp zu emulieren.

Im folgenden Beispiel wird ein allgemeiner Tabellenausdruck namens Numbers mit einer Spalte i generiert, die eine Zeile für die Nummern 1-5 enthält:

--Give a table name `Numbers" and a column `i` to hold the numbers
WITH Numbers(i) AS (
    --Starting number/index
    SELECT 1
    --Top-level UNION ALL operator required for recursion
    UNION ALL
    --Iteration expression:
    SELECT i + 1
    --Table expression we first declared used as source for recursion
    FROM Numbers
    --Clause to define the end of the recursion
    WHERE i < 5
)
--Use the generated table expression like a regular table
SELECT i FROM Numbers;

Diese Methode kann mit einem beliebigen Zahlenintervall sowie mit anderen Datentypen verwendet werden.

rekursives Auflisten eines Teilbaums

WITH RECURSIVE ManagedByJames(Level, ID, FName, LName) AS (
    -- start with this row
    SELECT 1, ID, FName, LName
    FROM Employees
    WHERE ID = 1

    UNION ALL

    -- get employees that have any of the previously selected rows as manager
    SELECT ManagedByJames.Level + 1,
           Employees.ID,
           Employees.FName,
           Employees.LName
    FROM Employees
    JOIN ManagedByJames
        ON Employees.ManagerID = ManagedByJames.ID

    ORDER BY 1 DESC   -- depth-first search
)
SELECT * FROM ManagedByJames;

Oracle CONNECT BY-Funktionalität mit rekursiven CTEs

Die CONNECT BY-Funktionalität von Oracle bietet viele nützliche und nicht triviale Funktionen, die bei rekursiven SQL-Standard-CTEs nicht integriert sind. In diesem Beispiel werden diese Features (mit einigen zusätzlichen Ergänzungen) unter Verwendung der SQL Server-Syntax repliziert. Es ist für Oracle-Entwickler am nützlichsten, wenn viele Funktionen in ihren hierarchischen Abfragen in anderen Datenbanken fehlen, aber es zeigt auch, was allgemein mit einer hierarchischen Abfrage möglich ist.

  WITH tbl AS (
       SELECT id, name, parent_id
         FROM mytable)
     , tbl_hierarchy AS (
       /* Anchor */
       SELECT 1 AS "LEVEL"
            --, 1 AS CONNECT_BY_ISROOT
            --, 0 AS CONNECT_BY_ISBRANCH
            , CASE WHEN t.id IN (SELECT parent_id FROM tbl) THEN 0 ELSE 1 END AS CONNECT_BY_ISLEAF
            , 0 AS CONNECT_BY_ISCYCLE
            , '/' + CAST(t.id   AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_id
            , '/' + CAST(t.name AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_name
            , t.id AS root_id
            , t.*
         FROM tbl t
        WHERE t.parent_id IS NULL                            -- START WITH parent_id IS NULL
       UNION ALL
       /* Recursive */
       SELECT th."LEVEL" + 1 AS "LEVEL"
            --, 0 AS CONNECT_BY_ISROOT
            --, CASE WHEN t.id IN (SELECT parent_id FROM tbl) THEN 1 ELSE 0 END AS CONNECT_BY_ISBRANCH
            , CASE WHEN t.id IN (SELECT parent_id FROM tbl) THEN 0 ELSE 1 END AS CONNECT_BY_ISLEAF
            , CASE WHEN th.SYS_CONNECT_BY_PATH_id LIKE '%/' + CAST(t.id AS VARCHAR(MAX)) + '/%' THEN 1 ELSE 0 END AS CONNECT_BY_ISCYCLE
            , th.SYS_CONNECT_BY_PATH_id   + CAST(t.id   AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_id
            , th.SYS_CONNECT_BY_PATH_name + CAST(t.name AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_name
            , th.root_id
            , t.*
         FROM tbl t
              JOIN tbl_hierarchy th ON (th.id = t.parent_id) -- CONNECT BY PRIOR id = parent_id
        WHERE th.CONNECT_BY_ISCYCLE = 0)                     -- NOCYCLE
SELECT th.*
     --, REPLICATE(' ', (th."LEVEL" - 1) * 3) + th.name AS tbl_hierarchy
  FROM tbl_hierarchy th
       JOIN tbl CONNECT_BY_ROOT ON (CONNECT_BY_ROOT.id = th.root_id)
 ORDER BY th.SYS_CONNECT_BY_PATH_name;                       -- ORDER SIBLINGS BY name

CONNECT BY-Funktionen, die oben gezeigt wurden, mit Erklärungen:

• Klauseln
  • CONNECT BY: Gibt die Beziehung an, die die Hierarchie definiert.
  • START WITH: Bestimmt die Wurzelknoten.
  • BESTELLEN VON SIBLINGS BY: Ordnet die Ergebnisse ordnungsgemäß an.
• Parameter
  • NOCYCLE: Stoppt die Verarbeitung einer Verzweigung, wenn eine Schleife erkannt wird. Gültige Hierarchien sind gerichtete azyklische Diagramme, und Zirkelverweise verletzen dieses Konstrukt.
• Operatoren
  • PRIOR: Erhält Daten vom übergeordneten Knoten des Knotens.
  • CONNECT_BY_ROOT: Ermittelt Daten vom Stamm des Knotens.
• Pseudospalten
  • LEVEL: Gibt die Entfernung des Knotens von der Wurzel an.
  • CONNECT_BY_ISLEAF: Gibt einen Knoten ohne untergeordnete Elemente an.
  • CONNECT_BY_ISCYCLE: Gibt einen Knoten mit einer Zirkelreferenz an.
• Funktionen
  • SYS_CONNECT_BY_PATH: Gibt eine abgeflachte / verkettete Darstellung des Pfads zum Knoten vom Stamm aus zurück.

Generieren Sie rekursiv Datumsangaben, die beispielsweise um Teamplanungen erweitert werden

DECLARE @DateFrom DATETIME = '2016-06-01 06:00'
DECLARE @DateTo DATETIME = '2016-07-01 06:00'
DECLARE @IntervalDays INT = 7

-- Transition Sequence = Rest & Relax into Day Shift into Night Shift
-- RR (Rest & Relax) = 1
-- DS (Day Shift) = 2
-- NS (Night Shift) = 3

;WITH roster AS
(
   SELECT @DateFrom AS RosterStart, 1 AS TeamA, 2 AS TeamB, 3 AS TeamC
   UNION ALL
   SELECT DATEADD(d, @IntervalDays, RosterStart),
          CASE TeamA WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 1 END AS TeamA,
          CASE TeamB WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 1 END AS TeamB,
          CASE TeamC WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 1 END AS TeamC
   FROM roster WHERE RosterStart < DATEADD(d, -@IntervalDays, @DateTo)
)

SELECT RosterStart,
       ISNULL(LEAD(RosterStart) OVER (ORDER BY RosterStart), RosterStart + @IntervalDays) AS RosterEnd,
       CASE TeamA WHEN 1 THEN 'RR' WHEN 2 THEN 'DS' WHEN 3 THEN 'NS' END AS TeamA,
       CASE TeamB WHEN 1 THEN 'RR' WHEN 2 THEN 'DS' WHEN 3 THEN 'NS' END AS TeamB,
       CASE TeamC WHEN 1 THEN 'RR' WHEN 2 THEN 'DS' WHEN 3 THEN 'NS' END AS TeamC
FROM roster

Ergebnis

Dh für Woche 1 ist TeamA auf R & R, TeamB auf Tagschicht und TeamC auf Nachtschicht.

Refactoring einer Abfrage zur Verwendung von Common Table-Ausdrücken

Angenommen, wir möchten alle Produktkategorien mit einem Gesamtumsatz von mehr als 20 erreichen.

Hier ist eine Abfrage ohne Common Table Expressions:

SELECT category.description, sum(product.price) as total_sales
FROM sale
LEFT JOIN product on sale.product_id = product.id
LEFT JOIN category on product.category_id = category.id
GROUP BY category.id, category.description
HAVING sum(product.price) > 20

Und eine gleichwertige Abfrage mit Common Table Expressions:

WITH all_sales AS (
  SELECT product.price, category.id as category_id, category.description as category_description
  FROM sale
  LEFT JOIN product on sale.product_id = product.id
  LEFT JOIN category on product.category_id = category.id
)
, sales_by_category AS (
  SELECT category_description, sum(price) as total_sales
  FROM all_sales
  GROUP BY category_id, category_description
)
SELECT * from sales_by_category WHERE total_sales > 20

Beispiel für eine komplexe SQL mit Common Table Expression

Angenommen, wir möchten die "billigsten Produkte" aus den "Top-Kategorien" abfragen.

Hier ist ein Beispiel für eine Abfrage mit Common Table Expressions

-- all_sales: just a simple SELECT with all the needed JOINS
WITH all_sales AS (
  SELECT
  product.price as product_price,
  category.id as category_id,
  category.description as category_description
  FROM sale
  LEFT JOIN product on sale.product_id = product.id
  LEFT JOIN category on product.category_id = category.id
)
-- Group by category
, sales_by_category AS (
  SELECT category_id, category_description,
  sum(product_price) as total_sales
  FROM all_sales
  GROUP BY category_id, category_description
)
-- Filtering total_sales > 20
, top_categories AS (
  SELECT * from sales_by_category WHERE total_sales > 20
)
-- all_products: just a simple SELECT with all the needed JOINS
, all_products AS (
  SELECT
  product.id as product_id,
  product.description as product_description,
  product.price as product_price,
  category.id as category_id,
  category.description as category_description
  FROM product
  LEFT JOIN category on product.category_id = category.id
)
-- Order by product price
, cheapest_products AS (
  SELECT * from all_products
  ORDER by product_price ASC
)
-- Simple inner join 
, cheapest_products_from_top_categories AS (
  SELECT product_description, product_price
  FROM cheapest_products
  INNER JOIN top_categories ON cheapest_products.category_id = top_categories.category_id
)
--The main SELECT
SELECT * from cheapest_products_from_top_categories