Chapter 05 - Common Table Expressions

Common Table Expressions

CTE u prezantuan me SQL Server 2005. CTE eshte nje bashkesi rreshtash e perkoheshme e emeruar. Kjo bashkesi rreshtash mund te referohet nga nje komande SELECT, INSERT, UPDATE, ose DELETE .

Nje CTE mund te perdoret gjithashtu ne nje komande CREATE VIEW , si pjese e komandes SELECT qe formon VIEW.

Gjithashtu ne SQL 2008 CTE mund te perdoret me nje komandde MERGE.

SQL Server ka dy tipe CTE—rekorsive dhe jo rekursive.

Per te krijuar nje CTE vendoset nje klauzole WITH menjehere perpara komandes SELECT, INSERT, UPDATE, DELETE, apo MERGE.

Klauzola WITH mund te perfshije disa CTE.

SIntaksa e pergjithshme per krijimin e CTE eshte:

WITH <common_table_expression> [,...]]

<common_table_expression>::=

cte_name [(column_name [,...])]

AS (cte_query)

...qe shpjegohet ne skicen e meposhtme...

Pasi krjohet CTE atehere ajo mund te perdoret si te ishte njew tabele e perkoheshme, jeta e se ciles zgjat vetem gjate ekzekutimit te query.

Si krijohen CTE jo rekursive

CTE jo rekursive quhet ajo CTE qe nuk referon vetveten nga brenda CTE. Shembulli me poshte krijon nje CTE me emrin empsales me dy kollona empid dhe sales.

WITH empsales(empid,sales)

AS

(

SELECT EMPLOYEEID,

COUNT(OrderId)

from orders

group by employeeid

)

select * from empsales

Pasi eshte krijuar empsales mund te perdoret si nje tabele ne te cilen kemi ekzekuar nje SELECT.

Numri i kollonave ne klaozolen WITH percakton numrin e kollonave qe duhet te ktheje query brenda kllapave ne klauzolen AS.

komanda me poshte

WITH empsales(empid,sales)

AS

(

SELECT EMPLOYEEID,

shipcountry,

COUNT(OrderId)

from orders

group by employeeid,ShipCountry

)

select * from empsales

do te jepte nje mesazh gabimi te ngjashem me:

Msg 8158, Level 16, State 1, Line 1 'empsales' has more columns than were specified in the column list.

Arsyeja per kete eshte fakti qe ne klauzolen width ne percaktojme nje CTE me dy kollona, kodin e punonjesit dhe numrin e porosive qe ai ka kryer. Ndersa ne klauzolen AS kemi nje komande SELECT qe kthen nje rezultat me tre rreshta kodin e punonjesit, emrin e shtetit dhe numrin e porosive per ate shtet nga ai punonjes. Duke qene se numri i kollonave eshte i ndryshem SQL afishon nje mesazh gabimi.

CTE mund te perdoren ne komande SELECT komplekse qe permbajne JOIN me tabela te tjera per shembull komanda:

WITH empsales(empid,sales)

AS

(

SELECT EMPLOYEEID,

COUNT(OrderId)

from orders

group by employeeid

)

select empid,FirstName,LastName,Sales

from empsales INNER JOIN Employees

On empsales.empid=Employees.EmployeeID

afishon kodin,emrin,mbiemrin dhe numrin e porosive te cdo punonjesi.

Disa CTE ne nje klauzole WITH

Eshte e mundur te kemi disa CTE te ndara me presje brenda nje klauzole WITH. Shembulli i meposhtem afishon shitjet per cdo shtet ne vitin 1996 dhe 1997

Ushtrim

Te afishojme shitjet(numer porosish) per cdo shtet (ShipCountry) per sejcilin nga vitet, 1996,1997,1998 ne nje format te gatshem per te ndertuar nje grafik , si meposhte

Zgjidhja me poshte

with

sales1996(Country,sales_1996)

as

(

select ShipCountry,COUNT(OrderID)

from Orders

where OrderDate between '1996-1-1' and '1997-1-1'

group by ShipCountry

) ,

sales1997(Country,sales_1997)

as

(

select ShipCountry,COUNT(OrderID)

from Orders

where OrderDate between '1997-1-1' and '1998-1-1'

group by ShipCountry

)

select sales1996.Country,sales_1996,sales_1997

from sales1996 INNER JOIN Sales1997

on sales1996.Country=sales1997.Country

CTE Rekursive

CTE quhet rekursive kur brenda komandes SELECT qe formon CTE referohet emri i CTE.

CTE rekursive jane shume te dobishme kur punojme me te dhena hierarkike pasi CTE vazhdon te ekzekutohet deri sa query te ktheje te gjithe te dhenat.

Per te ilustruar CTE rekursive do te perdorim tabelen punonjesit. Ne kollonen ReportsTo tek atbele Employees ruajme person tek i cili punonjesi raporton.

Nese na kerkohet te prodhojme nje strukture organizative te kompanise CTE rekursive eshte nje zgjidhje shume komode.

Megjithate duhet te jemi te kujdesshem pasi nje CTE e formuluar gabim mund te futet ne nje cikel te pafundem. Per te shmangur kete gje mund te perdorim opsionin MAXRECURSION ne komanden e jashtme SELECT, INSERT, UPDATE, DELETE, apo MERGE qe e perdor CTE.

Per me shume informacion ne lidhje me hint e query mund ti referohemi MSDN online.

http://msdn.microsoft.com/en-us/library/ms181714.aspx

Komanda me poshte afishon te dhenat e punonjesve ne menyre hierarkike duke perdorur CTE rekursive

WITH

cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)

AS

(

SELECT EmployeeID, FirstName, LastName, ReportsTo, 1

FROM Employees

WHERE ReportsTo IS NULL

UNION ALL

SELECT e.EmployeeID, e.FirstName, e.LastName, e.ReportsTo,

r.EmpLevel + 1

FROM Employees e

INNER JOIN cteReports r

ON e.ReportsTo = r.EmpID

)

SELECT

FirstName + ' ' + LastName AS FullName,

EmpLevel,

(SELECT FirstName + ' ' + LastName FROM Employees

WHERE EmployeeID = cteReports.MgrID) AS Manager

FROM cteReports

ORDER BY EmpLevel, MgrID

Versioni 2

WITH

cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)

AS

(

SELECT EmployeeID, FirstName, LastName, ReportsTo, 1

FROM Employees

WHERE ReportsTo IS NULL

UNION ALL

SELECT e.EmployeeID, e.FirstName, e.LastName, e.ReportsTo,

r.EmpLevel + 1

FROM Employees e

INNER JOIN cteReports r

ON e.ReportsTo = r.EmpID

)

select cte.FirstName+' '+cte.LastName as fullname,

cte.EmpLevel,

e.FirstName+' '+e.LastName as Manager

from cteReports cte

left join Employees e on cte.MgrID=e.EmployeeID