Masa Comună Expresii sau CTE pentru scurt sunt utilizate în SQL Server pentru a simplifica complexe se alătură și subinterogari, și de a oferi un mijloc pentru a interoga datele ierarhice, cum ar fi o organigramă. În acest articol, vă vom prezenta expresii comune de masă, cele două tipuri de CTEs și utilizările acestora. În plus, vom introduce CTE pe ansamblu., Odată ce sunteți familiarizați, vă încurajez să citiți și aceste articole:
- Non recursive CTE lui
- Recursive CTE lui
Introducere în expresii de masă comune
un CTE (expresie de masă comună) este un set de rezultate temporare pe care le puteți face referire într-o altă instrucțiune Selectați, Inserați, actualizați sau ștergeți. Acestea au fost introduse în versiunea SQL Server 2005. Acestea sunt compatibile cu SQL și fac parte din specificația ANSI SQL 99.un CTE returnează întotdeauna un set de rezultate., Acestea sunt utilizate pentru a simplifica interogările, de exemplu, puteți utiliza unul pentru a elimina un tabel derivat din corpul principal de interogare.Notă: toate exemplele pentru această lecție se bazează pe Microsoft SQL Server Management Studio și baza de date AdventureWorks2012. Puteți începe să utilizați aceste instrumente gratuite folosind ghidul meu Noțiuni de bază folosind SQL Server.
ce este o expresie CTE sau comună de masă în SQL Server?
un CTE (Common Table Expression) definește un set de rezultate temporar pe care îl puteți folosi apoi într-o instrucțiune SELECT. Devine o modalitate convenabilă de a gestiona interogări complicate.,
expresiile comune ale tabelelor sunt definite în cadrul instrucțiunii folosind operatorul WITH. Puteți defini una sau mai multe expresii de masă comune în acest mod.
aici este un exemplu foarte simplu de un CTE:
WITH Employee_CTE (EmployeeNumber, Title)AS(SELECT NationalIDNumber, JobTitle FROM HumanResources.Employee)SELECT EmployeeNumber, TitleFROM Employee_CTE
să rupe acest lucru în jos un pic.
Partea albastră este la CTE. Observați că conține o interogare care poate fi rulat pe cont propriu în SQL., Acest lucru este numit CTE interogare de definiție:
SELECT NationalIDNumber, JobTitleFROM HumanResources.Employee
atunci Când îl rulați vezi rezultate, cum ar fi:
Observați că, atunci când definim CTE ne da rezultatul un nume precum coloanele sale. În acest fel, un CTE acționează ca o vedere. Rezultatul și coloanele sunt denumite diferit. Acest lucru vă permite să încapsulați logica de interogare complicată cu expresia comună a tabelului.acum, revenind la CTE, observați că declarația WITH., Acolo veți vedea numele și coloanele sunt definite. Aceste coloane corespund coloanelor returnate din interogarea interioară.
în cele din Urmă observa că ultimul nostru de interogare referințe la CTE și coloane definite.
Din perspectiva interogării noastre exterioare, tot ce „vede” este această definiție. Nu este preocupat de modul în care este construit CTE, doar numele și coloanele sale.,
ca atare, rezultatele de la CTE sunt:
observați numele coloanelor, acestea se bazează pe cele definite în CTE.
vreau să subliniez că puteți defini mai multe CTE într-o declarație cu. Acest lucru vă poate ajuta să simplificați unele interogări foarte complicate, care sunt în cele din urmă Unite. Fiecare piesă complicată poate include în propria lor CTE, care este apoi menționată și s-au alăturat în afara cu clauza.,
aici este un exemplu folosind două CTE, este un exemplu simplu, dar arată cum sunt definite două CTE și apoi utilizate într-o îmbinare interioară
WITH PersonCTE (BusinessEntityID, FirstName, LastName)AS (SELECT Person.BusinessEntityID, FirstName, LastName FROM Person.Person WHERE LastName LIKE 'C%'),PhoneCTE (BusinessEntityID, PhoneNumber)AS (SELECT BusinessEntityID, PhoneNumber FROM Person.PersonPhone)SELECT FirstName, LastName, PhoneNumberFROM PersonCTEINNER JOINPhoneCTEON PersonCTE.BusinessEntityID = PhoneCTE.BusinessEntityID;
prima expresie comună a tabelului este colorată verde, a doua albastră. După cum puteți vedea din instrucțiunea SELECT, CTE-urile sunt unite ca și cum ar fi tabele. Sperăm că puteți vedea că, pe măsură ce interogările dvs. devin mai complicate, CTE-urile pot deveni o modalitate foarte utilă de a separa operațiunile; prin urmare, simplificați interogarea finală.
De ce aveți nevoie de CTE?,
există mai multe motive pentru care poate doriți să utilizați un CTE peste alte metode. Unele dintre ele includ:
- lizibilitate-CTE promovează lizibilitatea. Mai degrabă decât forfetare tot ce interogare logica într-o singură interogare mare, a crea mai multe CTE, care sunt combinate mai târziu în declarația. Acest lucru vă permite să obțineți bucățile de date de care aveți nevoie și să le combinați într-o selecție finală.
- substitut pentru o vizualizare-puteți înlocui un CTE pentru o vizualizare. Acest lucru este util dacă nu aveți permisiuni pentru a crea un obiect de vizualizare sau nu doriți să creați unul, deoarece este utilizat numai în această interogare.,
- Recursivitate-utilizați CTE ‘ s creează interogări recursive, adică interogări care se pot numi singure. Acest lucru este util atunci când trebuie să lucrați la date ierarhice, cum ar fi organigrame.
- limitări-depășiți limitările anumitor declarații, cum ar fi referențierea în sine (recursivitate) sau efectuarea grupului folosind funcții nedeterministe.
- clasament-ori de câte ori doriți să utilizați funcția de clasare, cum ar fi ROW_NUMBER (), RANK (), NTILE() etc.
tipuri de CTE lui
expresii de masă comune pot fi plasate în două mari categorii: Recursive CTE și Non-Recursive CTE lui.,
CTE Recursive sunt expresii comune de masă care se referă la ele însele. Recursivitatea poate fi un subiect destul de dificil de înțeles, chiar nu l-am obținut până nu am luat o clasă LISP înapoi în 1986, dar sperăm că vă pot explica.
vom merge adânc în recursivitate într-o postare separată, dar pentru moment permiteți-mi să vă prezint recursivitate folosind această diagramă:
aici vedeți imaginea oglinzilor opuse. Datorită reflecției, devine o imagine într-o imagine.
interogările Recursive sunt așa.,
pe măsură ce o interogare recursivă este rulată, aceasta rulează în mod repetat pe un subset de date. O interogare recursivă este de fapt o interogare care se numește. La un moment dat există o condiție finală, deci nu se numește pe termen nelimitat.
într-un fel atunci când te uiți în imagine vă puteți imagina fiecare imagine într-o imagine este imaginea de asteptare în sine. Cu toate acestea, spre deosebire de „reflecția infinită” din oglinzi, vine un punct în care o interogare recursivă întâlnește condiția finală și nu se mai numește.,în acel moment, recursivitatea începe să se desfășoare, să colecteze și să calculeze datele pe măsură ce analizează fiecare rezultat succesiv.
Non-Recursive CTE, după cum sugerează și numele, sunt nu utilizați recursivitate. Ei nu se referă la ei înșiși. Ele sunt mai ușor de înțeles, așa că le vom examina mai întâi în detaliu în următorul articol din această serie.
concluzie
sperăm că acum aveți o apreciere a ceea ce sunt CTE și de ce am putea dori să le folosească. În următoarele două articol vom merge în detaliu mult mai mare pe CTE, și când să le folosească.,
până atunci, aș recomanda revizuirea articolelor mele despre joins și subqueries, deoarece vom folosi aceste tipuri de interogări pentru exemplele noastre.