SQL Server/T-SQL Tutorial/Transact SQL/With
Содержание
Common Table Expressions
<source lang="sql">
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)</source>
Select from Common Table Expressions
<source lang="sql">
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</source>
Syntax for a CTE for recursive queries is
<source lang="sql">
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></source>
With as
<source lang="sql">
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></source>
