SQL Server/T-SQL Tutorial/Transact SQL/With
Содержание
Common Table Expressions
A Common Table Expression (CTE) creates a temporary query that can be referenced within the scope of a SELECT, INSERT, UPDATE, or DELETE query.
The basic syntax for a CTE is as follows:
WITH expression_name [ ( column_name [ ,...n ] ) ]
AS ( CTE_query_definition )
The arguments of a CTE are described in the following table.
Argument Description
expression_name The name of the common table expression.
column_name [ ,...n ] The unique column names of the expression.
CTE_query_definition The SELECT query that defines the common table expression.
A non-recursive CTE is one that is used within a query without referencing itself.
It serves as a temporary result set for the query.
A recursive CTE is defined similarly to a non-recursive CTE, only a recursive CTE returns hierarchical self-relating data.
Using a CTE to represent recursive data can minimize the amount of code needed compared to other methods.
Referenced from:
SQL Server 2005 T-SQL Recipes A Problem-Solution Approach
5> create table freights
6> (orderid INT NOT NULL,
7> orderdate DATETIME,
8> shippeddate DATETIME,
9> freight MONEY,
10> price MONEY)
11> GO
1> INSERT INTO freights VALUES (1111, "1.10.2005","1.20.2005", 30.45, 200.25)
2> INSERT INTO freights VALUES (2222, "2.11.2005", "2.21.2005", 89.25, 543.00)
3> INSERT INTO freights VALUES (3333, "3.12.2005", "3.22.2005", 19.35, 120.25)
4> INSERT INTO freights VALUES (4444, "4.13.2005", "4.23.2005", 9.99, 154.35)
5> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> select orderid from freights
2> where price > (SELECT AVG(price) from freights WHERE YEAR(orderdate) = "2005")
3> AND freight > (SELECT AVG(price)
4> from freights
5> WHERE YEAR(orderdate) = "2005")/10
6> GO
orderid
-----------
2222
(1 rows affected)
1> --A better way is to write a common table expression using the WITH clause.
2>
3> WITH price_calc (year_2005) AS
4> (SELECT AVG(price)
5> from freights
6> WHERE YEAR(orderdate)="2005")
7> SELECT orderid
8> FROM freights
9> WHERE price > (SELECT year_2005
10> FROM price_calc)
11> AND freight > (SELECT year_2005
12> FROM price_calc)/10;
13> drop table freights;
14> GO
orderid
-----------
2222
(1 rows affected)
Select from Common Table Expressions
91>
92> CREATE TABLE employee(
93> id INTEGER NOT NULL PRIMARY KEY,
94> first_name VARCHAR(10),
95> last_name VARCHAR(10),
96> salary DECIMAL(10,2),
97> start_Date DATETIME,
98> region VARCHAR(10),
99> city VARCHAR(20),
100> managerid INTEGER
101> );
102> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3> WITH MySearch (ID, first_Name)
4> AS
5> (
6> SELECT ID,first_Name
7> FROM employee
8> )
9>
10> SELECT v.first_Name
11> FROM MySearch v
12> GO
first_Name
----------
Jason
Alison
James
Celia
Robert
Linda
David
James
Joan
(9 rows affected)
1>
2>
3>
4>
5> drop table employee;
6> GO
Syntax for a CTE for recursive queries is
5>
6> CREATE TABLE airplane
7> (containing_assembly VARCHAR(10),
8> contained_assembly VARCHAR(10),
9> quantity_contained INT,
10> unit_cost DECIMAL (6,2))
11> GO
1> insert into airplane values ("Airplane", "Fuselage", 1, 10)
2> insert into airplane values ("Airplane", "Wings", 1, 11)
3> insert into airplane values ("Airplane", "Tail", 1, 12)
4> insert into airplane values ("Fuselage", "Cockpit", 1, 13)
5> insert into airplane values ("Fuselage", "Cabin", 1, 14)
6> insert into airplane values ("Fuselage", "Nose", 1, 15)
7> insert into airplane values ("Cockpit", NULL, 1, 13)
8> insert into airplane values ("Cabin", NULL, 1, 14)
9> insert into airplane values ("Nose", NULL, 1, 15)
10> insert into airplane values ("Wings", NULL, 2, 11)
11> insert into airplane values ("Tail", NULL, 1, 12)
12> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> --The following example shows the use of the WITH clause to define a query that calculates the total costs of each assembly.
2>
3> WITH list_of_parts(assembly, quantity, cost) AS
4> (SELECT containing_assembly, quantity_contained, unit_cost
5> FROM airplane
6> WHERE contained_assembly IS NULL
7> UNION ALL
8> SELECT a.containing_assembly, a.quantity_contained,
9> CAST(l.quantity*l.cost AS DECIMAL(6,2))
10> FROM list_of_parts l,airplane a
11> WHERE l.assembly = a.contained_assembly)
12> SELECT * FROM list_of_parts
13> GO
assembly quantity cost
---------- ----------- --------
Cockpit 1 13.00
Cabin 1 14.00
Nose 1 15.00
Wings 2 11.00
Tail 1 12.00
Cockpit 1 13.00
Cabin 1 14.00
Nose 1 15.00
Wings 2 11.00
Tail 1 12.00
Cockpit 1 13.00
Cabin 1 14.00
Nose 1 15.00
Wings 2 11.00
Tail 1 12.00
Airplane 1 12.00
Airplane 1 12.00
Airplane 1 12.00
Airplane 1 22.00
Airplane 1 22.00
Airplane 1 22.00
Fuselage 1 15.00
Fuselage 1 15.00
Fuselage 1 15.00
Airplane 1 15.00
Airplane 1 15.00
Airplane 1 15.00
Airplane 1 15.00
Airplane 1 15.00
Airplane 1 15.00
Airplane 1 15.00
Airplane 1 15.00
Airplane 1 15.00
Fuselage 1 14.00
Fuselage 1 14.00
Fuselage 1 14.00
Airplane 1 14.00
Airplane 1 14.00
Airplane 1 14.00
Airplane 1 14.00
Airplane 1 14.00
Airplane 1 14.00
Airplane 1 14.00
Airplane 1 14.00
Airplane 1 14.00
Fuselage 1 13.00
Fuselage 1 13.00
Fuselage 1 13.00
Airplane 1 13.00
Airplane 1 13.00
Airplane 1 13.00
Airplane 1 13.00
Airplane 1 13.00
Airplane 1 13.00
Airplane 1 13.00
Airplane 1 13.00
Airplane 1 13.00
Airplane 1 12.00
Airplane 1 12.00
Airplane 1 12.00
Airplane 1 22.00
Airplane 1 22.00
Airplane 1 22.00
Fuselage 1 15.00
Fuselage 1 15.00
Fuselage 1 15.00
Airplane 1 15.00
Airplane 1 15.00
Airplane 1 15.00
Airplane 1 15.00
Airplane 1 15.00
Airplane 1 15.00
Airplane 1 15.00
Airplane 1 15.00
Airplane 1 15.00
Fuselage 1 14.00
Fuselage 1 14.00
Fuselage 1 14.00
Airplane 1 14.00
Airplane 1 14.00
Airplane 1 14.00
Airplane 1 14.00
Airplane 1 14.00
Airplane 1 14.00
Airplane 1 14.00
Airplane 1 14.00
Airplane 1 14.00
Fuselage 1 13.00
Fuselage 1 13.00
Fuselage 1 13.00
Airplane 1 13.00
Airplane 1 13.00
Airplane 1 13.00
Airplane 1 13.00
Airplane 1 13.00
Airplane 1 13.00
Airplane 1 13.00
Airplane 1 13.00
Airplane 1 13.00
Airplane 1 12.00
Airplane 1 12.00
Airplane 1 12.00
Airplane 1 22.00
Airplane 1 22.00
Airplane 1 22.00
Fuselage 1 15.00
Fuselage 1 15.00
Fuselage 1 15.00
Airplane 1 15.00
Airplane 1 15.00
Airplane 1 15.00
Airplane 1 15.00
Airplane 1 15.00
Airplane 1 15.00
Airplane 1 15.00
Airplane 1 15.00
Airplane 1 15.00
Fuselage 1 14.00
Fuselage 1 14.00
Fuselage 1 14.00
Airplane 1 14.00
Airplane 1 14.00
Airplane 1 14.00
Airplane 1 14.00
Airplane 1 14.00
Airplane 1 14.00
Airplane 1 14.00
Airplane 1 14.00
Airplane 1 14.00
Fuselage 1 13.00
Fuselage 1 13.00
Fuselage 1 13.00
Airplane 1 13.00
Airplane 1 13.00
Airplane 1 13.00
Airplane 1 13.00
Airplane 1 13.00
Airplane 1 13.00
Airplane 1 13.00
Airplane 1 13.00
Airplane 1 13.00
(141 rows affected)
1>
2> drop table airplane;
3> GO
1>
With as
20> CREATE TABLE Employees (
21> EmployeeID int IDENTITY (1, 1) NOT NULL ,
22> LastName nvarchar (20) NOT NULL ,
23> FirstName nvarchar (10) NOT NULL ,
24> Title nvarchar (30) NULL ,
25> TitleOfCourtesy nvarchar (25) NULL ,
26> BirthDate datetime NULL ,
27> HireDate datetime NULL ,
28> Address nvarchar (60) NULL ,
29> City nvarchar (15) NULL ,
30> Region nvarchar (15) NULL ,
31> PostalCode nvarchar (10) NULL ,
32> Country nvarchar (15) NULL ,
33> HomePhone nvarchar (24) NULL ,
34> Extension nvarchar (4) NULL ,
35> Photo image NULL ,
36> Notes ntext NULL ,
37> ReportsTo int NULL ,
38> PhotoPath nvarchar (255) NULL
39>
40> )
41> GO
1>
2>
3>
4> WITH Args1 AS
5> (
6> SELECT LastName, FirstName, BirthDate,
7> DATEDIFF(year, BirthDate, GETDATE()) AS Diff,
8> CAST(CONVERT(CHAR(8), GETDATE(), 112) AS DATETIME) AS Today
9> FROM dbo.Employees
10> ),
11>
12> Args2 AS
13> (
14> SELECT LastName, FirstName, BirthDate, Today,
15> DATEADD(year, Diff, BirthDate) AS BDCur,
16> DATEADD(year, Diff + 1, BirthDate) AS BDNxt
17> FROM Args1
18> ),
19> Args3 AS
20> (
21> SELECT LastName, FirstName, BirthDate, Today,
22> BDCur + CASE WHEN DAY(BirthDate) = 29 AND DAY(BDCur) = 28
23> THEN 1 ELSE 0 END AS BDCur,
24> BDNxt + CASE WHEN DAY(BirthDate) = 29 AND DAY(BDNxt) = 28
25> THEN 1 ELSE 0 END AS BDNxt
26> FROM Args2
27> )
28>
29> SELECT LastName, FirstName, BirthDate,
30> CASE WHEN BDCur >= Today THEN BDCur ELSE BDNxt END AS BirthDay
31> FROM Args3;
32>
33> drop table Employees;
34> GO
LastName FirstName BirthDate BirthDay
-------------------- ---------- ----------------------- -----------------------
Today Mary 2008-08-17 00:00:00.000 2008-08-17 00:00:00.000
1>