SQL MED klausul

I SQL er WITH-sætningen også kendt som CTE. Det er en kraftfuld funktion, der gør det muligt for os at oprette midlertidige resultatsæt i en forespørgsel. En vigtig rolle for CTE'er er at forenkle de komplekse forespørgsler til mindre og genbrugelige underforespørgsler. Dette er med til at gøre koden mere læsbar og vedligeholdelig i det lange løb.

Slut dig til os i denne øvelse, mens vi udforsker, hvordan de almindelige tabeludtryk fungerer ved hjælp af WITH-klausulen og understøttet funktionalitet.

Krav:

Til demonstrationsformål vil vi bruge følgende:

1. MySQL version 8.0 og nyere
2. Sakila prøvedatabase

Med de givne krav opfyldt, kan vi fortsætte med at lære mere om CTE'er og WITH-klausulen.

SQL MED klausul

WITH-sætningen giver os mulighed for at definere et eller flere midlertidige resultatsæt, der er kendt som almindelige tabeludtryk.

Vi kan referere til de resulterende CTE'er i hovedforespørgslen som enhver anden tabel eller resultatsæt. Dette spiller en afgørende rolle i at skabe modulære SQL-forespørgsler.

Selvom syntaksen for CTE kan variere lidt afhængigt af dine krav, viser det følgende den grundlæggende syntaks for CTE i SQL:

Vi starter med nøgleordet WITH, som fortæller SQL-databasen, at vi ønsker at oprette og bruge CTE.

Dernæst angiver vi navnet på CTE'en, som giver os mulighed for at referere til den i andre forespørgsler.

Vi angiver også en valgfri liste over kolonnenavne, hvis CTE indeholder kolonnealiasserne.

Dernæst fortsætter vi med at definere CTE-forespørgslen. Dette indeholder alle opgaver eller data, som CTE'en udfører, indesluttet i et par parenteser.

Til sidst specificerer vi hovedforespørgslen, som refererer til CTE.

Eksempel på brug:

En af de bedste måder at forstå, hvordan man bruger og arbejder med CTE'er, er at se på et praktisk eksempel.

Tag for eksempel Sakila-eksempeldatabasen. Antag, at vi ønsker at finde de 10 bedste kunder med det højeste antal lejemål.

Tag et kig på følgende viste CTE.

Brug af SQL WITH-klausulen til at finde de 10 bedste kunder med det højeste antal lejemål:

I det givne eksempel starter vi med at definere en ny CTE ved hjælp af nøgleordet WITH efterfulgt af det navn, som vi ønsker at tildele til CTE. I dette tilfælde kalder vi det 'CustomerRentals'.

Inde i CTE-organet beregner vi lejeantallet for hver kunde ved at tilslutte os kunde- og lejetabellen.

Til sidst, i hovedforespørgslen, vælger vi alle kolonnerne fra CTE, bestiller resultaterne baseret på lejeantallet (faldende rækkefølge) og begrænser outputtet til kun de øverste 10 rækker.

Dette giver os mulighed for at hente de kunder med det højeste antal lejemål som vist i følgende output:

Rekursive CTE'er

I nogle andre tilfælde har du måske at gøre med hierarkiske datastrukturer. Det er her de rekursive CTE'er kommer i spil.

Lad os for eksempel tage et tilfælde, hvor vi ønsker at navigere i den hierarkiske organisation eller repræsentere en trælignende struktur. Vi kan bruge søgeordet WITH RECURSIVE til at oprette en rekursiv CTE.

Da der ikke er hierarkiske data, som vi kan bruge i Sakila-databasen til at demonstrere en rekursiv CTE, lad os opstille et grundlæggende eksempel.

I dette tilfælde har vi en prøve 'afdeling' tabel med nogle tilfældige data. For at finde den hierarkiske struktur af afdelingerne kan vi bruge en rekursiv CTE som følger:

I dette tilfælde starter den rekursive CTE med afdelinger, der har et NULL 'parent_department_id' (rodafdelinger) og henter rekursivt de underordnede afdelinger.

Konklusion

I denne øvelse lærte vi om de mest grundlæggende og nyttige funktioner i SQL-databaser som de almindelige tabeludtryk ved at forstå, hvordan man arbejder med nøgleordet WITH.