Use the with statement to write a slightly complex SQL statement.

Today, I occasionally see the with keyword in SQL. I have been writing SQL statements for a few years, so I was the first to get in touch with it. After reading an article by a blogger, I added some content and made a simple summary. This statement was the first time I saw and learned. From simple to complex writing, I hope the experts will not laugh. The following SQL statement is designed for three tables. I used a txt file to copy the table content, here we may try to import these tables to the database using the method of creating an end-to-end package described in the previous article.

Simple aggregation

From the orders table, select the total number of customers who have subscribed to the product in each year.

First, we can write it like this.

 
 

  
  
select YEAR(o.orderdate) orderyear,COUNT(distinct(custid)) numCusts  
  
  
from Sales.Orders o  
  
  
group by YEAR(o.orderdate)  
  
  
go 
 
 

Note that if you replace group by YEAR (o. orderdata) with group by orderyear, an error will occur. The SQL statement execution sequence is involved here. You have time to understand it.

Method 2,

 
 

  
  
select orderyear,COUNT(distinct(custid))numCusts  
  
  
from (select YEAR(orderdate) as orderyear,custid from sales.orders) as D  
  
  
group by orderyear  
  
  
go  
 
 

First obtain orderyear in the from statement, and then select statement will not see the error without this field

The third method,

 
 

  
  
select orderyear,COUNT(distinct(custid)) numCusts  
  
  
from (select YEAR(orderdate),custid from sales.orders) as D(orderyear,custid)  
  
  
group by orderyear  
  
  
go  
 
 

Adding the selected field to the end of as D makes it clearer!

Fourth Writing Method: with is on stage

 
 

  
  
with c as(  
  
  
select YEAR(orderdate) orderyear, custid from sales.orders)  
  
  
select orderyear,COUNT(distinct(custid)) numCusts from c group by orderyear  
  
  
go  
 
 

With can make the statement more aggregated. The following is an authoritative explanation.

Specifies a result set with a temporary name. These result sets are called common table expressions (CTE ). This expression is derived from a simple query and is defined within the execution range of a single SELECT, INSERT, UPDATE, MERGE, or DELETE statement. This clause can also be used in the create view statement as part of the SELECT Definition Statement of the statement. Common table expressions can include references to themselves. This type of expression is called a recursive public expression.
---- MSDN

The fifth writing method can also be used for reference by the third writing method, which makes the statement clearer and easier to maintain.

 
 

  
  
with c(orderyear,custid) as(  
  
  
 select YEAR(orderdate),custid from sales.orders)  
  
  
 select orderyear,COUNT(distinct(custid)) numCusts from c group by c.orderyear  
  
  
 go  
 
 

The same result is obtained for all the writing methods in the preceding 5, for example, 1:

 

Figure 1

Add computing

It is required to calculate the number of customers that increase each year in the order table compared to the previous year. This is a little complicated.

 
 

  
  
with yearcount as(  
  
  
  select YEAR(orderdate) orderyear,COUNT(distinct(custid)) numCusts from sales.orders group by YEAR(orderdate))  
  
  
  select cur.orderyear curyear,cur.numCusts curNumCusts,prv.orderyear prvyear,prv.numCusts prvNumCusts,cur.numCusts-prv.numCusts growth  
  
  
  from yearcount cur left join yearcount prv on cur.orderyear=prv.orderyear+1  
  
  
 go  
 
 

The with result set is used twice. The query result is as follows: 2.

Figure 2

Complex computing

Find the customer id. These customers and all employees from the United States have at least one transaction record. The query statement is as follows:

 
 

  
  
with TheseEmployees as(  
  
  
select empid from hr.employees where country='USA'),  
  
  
CharacteristicFunctions as(  
  
  
select custid,  
  
  
       case when custid in (select custid from sales.orders as o where o.empid=e.empid) then 1 else 0 end as charfun  
  
  
from sales.customers as c cross join TheseEmployees as e)  
  
  
select custid,min(charfun) from CharacteristicFunctions group by custid having min(charfun)=1  
  
  
go  
 
 

The with statement is nested here. The with statement searches for the IDs of American employees. The second statement uses this result and the customer id and owner id to perform Cartesian Product Operations. Finally, we can find the final custid from this Descartes.

Result 3:

Figure 3

This is only a simple introduction, not in-depth, and experts should not laugh.