Cte SQL Server

SQL Server 2005 T-SQL: CTE

Concept:Common table expression (CTE) is a common table expression.

Usage:It is difficult to deal with SQL statements of previous versions, such as paging and recursive queries...

Basic usage:

 

With <name of your CTE> (<column names>)
As
(
<Actual query>
)

Select * from <name of your CTE>

 

Example 1 (basic usage ):

 

With mycte (ID, name)
As
(
Select employeeid as ID, firstname + ''+ lastname as name
From HumanResources. vemployee
)
Select * From mycte

 

Example 2 (paging ):

 

With mycte (ID, name, rowid)
As
(
Select employeeid as ID, firstname + ''+ lastname as name,
Row_number () over (order by employeeid) as rowid
From HumanResources. vemployee
)
Select * From mycte where rowid between 1 and 10

 

Example 3 (Associate CTE ):

 

With ordercountcte (salespersonid, ordercount)
As
(
Select salespersonid, count (1)
From sales. salesorderheader
Where salespersonid is not null
Group by salespersonid
)
Select sp. salespersonid, sp. salesytd, CTE. ordercount
From ordercountcte CTE inner join sales. SALESPERSON sp
On CTE. salespersonid = sp. salespersonid order by 3

Example 4 (delete using CTE ):

 

 

Create Table products (
Product_id int not null,
Product_name varchar (25 ),
Price money null,
Constraint pk_products primary key nonclustered (product_id)
)
Go
Insert into products (product_id, product_name, price) values (1, 'widgets', 25)
Insert into products (product_id, product_name, price) values (2, 'gadgets', 50)
Insert into products (product_id, product_name, price) values (3, 'thingies ', 75)
Insert into products (product_id, product_name, price) values (4, 'whoozits ', 90)
Insert into products (product_id, product_name, price) values (5, 'whatzits ', 5)
Insert into products (product_id, product_name, price) values (6, 'gizmos ', 15)
Insert into products (product_id, product_name, price) values (7, 'widgets', 24)
Insert into products (product_id, product_name, price) values (8, 'gizmos ', 36)
Insert into products (product_id, product_name, price) values (9, 'gizmos ', 36)
Go

-- ========================== Delete duplicate products ===================== ==============
With duplicateprodcte
As
(Select Min (product_id) as product_id, product_name
From Products
Group by product_name
Having count (1)> 1
)
Delete products from products P join duplicateprodcte CTE
On CTE. product_name = P. product_name and P. product_id> CTE. product_id

 

Example 5 (recursive query ):

Create Table employee_tree (employee_nm nvarchar (50), employee_id int primary key, reportsto INT)
-- Insert some data, build a reporting tree
Insert into employee_tree values ('Richard ', 1, null)
Insert into employee_tree values ('Stephen ', 2, 1)
Insert into employee_tree values ('clemens', 3, 2)
Insert into employee_tree values ('malek ', 4, 2)
Insert into employee_tree values ('goksin ', 5, 4)
Insert into employee_tree values ('Kimberly ', 6, 1)
Insert into employee_tree values ('ramesh ', 7, 5)
--
With mycte
As
(Select employee_id, employee_nm,-1 as reportsto, 0 as sublevel
From employee_tree where reportsto is null -- Root Node
Union all
Select E. employee_id, E. employee_nm, E. reportsto, sublevel + 1
From employee_tree E, mycte where E. reportsto = mycte. employee_id

) -- Select * From mycte
Select mycte. employee_nm as EMP, mycte. Sublevel, E. employee_nm as boss
From mycte left join employee_tree e on mycte. reportsto = E. employee_id
-- Option (maxrecursion 3) -- Error
-- Option (maxrecursion 4) -- OK
Where sublevel <4

Note: Option (maxrecursion 4) is used to set the number of layers to be searched during recursion. The default value is 100. If it exceeds the default value or the value is specified, an error is returned. we usually use a layer column to filter the specified layer.