Good Mood

samenvatting: in deze tutorial leert u meer over de common table expression of CTE in SQL Server door gebruik te maken van de WITH clausule.

Inleiding tot CTE in SQL Server

CTE staat voor common table expression. Met een CTE kunt u een tijdelijk benoemde resultaatset definiëren die tijdelijk beschikbaar is in het uitvoerbereik van een statement, zoals SELECT, INSERT, UPDATE, DELETE, of MERGE.,

het volgende toont de algemene syntaxis van een CTE in SQL Server:

Code language: SQL (Structured Query Language) (sql)

In deze syntaxis:

• geef eerst de naam van de expressie op (expression_name) waarnaar u later in een query kunt verwijzen.
• geef vervolgens een lijst met door komma ‘ s gescheiden kolommen op na de expression_name. Het aantal kolommen moet gelijk zijn aan het aantal kolommen gedefinieerd in de CTE_definition.
• gebruik dan het als sleutelwoord achter de expressienaam of kolomlijst als de kolomlijst is opgegeven.,
• definieer daarna eenSELECT statement waarvan de resultaatset de gemeenschappelijke tabeluitdrukking vult.
• ten slotte, refereer naar de gemeenschappelijke tabeluitdrukking in een query (SQL_statement) zoals SELECT, INSERT, UPDATE, DELETE, of MERGE.

We geven de voorkeur aan gemeenschappelijke tabeluitdrukkingen in plaats van subqueries te gebruiken omdat gemeenschappelijke tabeluitdrukkingen leesbaarder zijn., We gebruiken ook CTE in de queries die analytische functies (of vensterfuncties)

SQL Server CTE voorbeelden

laten we enkele voorbeelden nemen van het gebruik van gemeenschappelijke tabeluitdrukkingen.

a) Simple SQL Server CTE example

deze query gebruikt een CTE om de verkoopbedragen door verkoopmedewerkers in 2018 te retourneren:

de volgende afbeelding toont de resultatenreeks:

In dit voorbeeld:

• eerst definieerden we cte_sales_amounts als de naam van de gemeenschappelijke tabeluitdrukking., de CTE geeft een resultaat terug dat bestaat uit drie kolommen staff, year, en sales afgeleid van de definitiequery.
• ten tweede hebben we een query geconstrueerd die het totale verkoopbedrag per verkoopmedewerker en jaar retourneert door gegevens te bevragen uit de orders, order_items en staffs tabellen.
• Ten derde verwijzen we naar de CTE in de buitenste query en selecteren we alleen de rijen waarvan het jaar 2018 is.,

merkte op dat dit voorbeeld uitsluitend bedoeld is om u te helpen geleidelijk te begrijpen hoe veelvoorkomende tabeluitdrukkingen werken. Er is een meer optimale manier om het resultaat te bereiken zonder CTE te gebruiken.

B) met behulp van een gemeenschappelijke tabeluitdrukking om rapportgemiddelden te maken op basis van Tellingen

Dit voorbeeld gebruikt de CTE om het gemiddelde aantal verkooporders in 2018 voor alle sales staff te retourneren.

Hier is de uitvoer:

Code language: SQL (Structured Query Language) (sql)

In dit voorbeeld:

eerst gebruikten we cte_sales als de naam van de gemeenschappelijke tabeluitdrukking., We hebben de kolomlijst van de CTE overgeslagen, dus het is afgeleid van het CTE definition statement. In dit voorbeeld bevat het staff_id en order_count kolommen.

ten tweede gebruiken we de volgende query om de resultaatset te definiëren die de gemeenschappelijke tabeluitdrukking opvult cte_sales. De query retourneert het aantal bestellingen in 2018 door verkoopmedewerkers.

ten derde verwijzen we naar de cte_sales in de Outer statement en gebruiken de AVG() functie om de gemiddelde verkooporder te krijgen door alle stafleden.,

Code language: SQL (Structured Query Language) (sql)

C) meerdere SQL Server CTE gebruiken in één query voorbeeld

het volgende voorbeeld gebruikt twee CTE cte_category_counts en cte_category_sales om het aantal producten en verkopen voor elke productcategorie te retourneren. De buitenste query voegt twee CTE ‘ s samen met de kolom category_id.,

Hier is het resultaat:

In deze tutorial hebt u geleerd hoe u gemeenschappelijke tabeluitdrukkingen of CTE in SQL Server kunt gebruiken om complexe query ‘ s op een eenvoudig te begrijpen manier te construeren.