How to Use CTE in sqlserver to solve complex query problems? sqlservercte Query

How to Use CTE in sqlserver to solve complex query problems? sqlservercte Query

Recently, colleagues have to query users' business and report data from several tables and write an SQL statement, which is slow:

Select S.Name,S.AccountantCode,(Select COUNT(*) from (Select Distinct BusinessBackupId from Biz_BusinessBackupCustomer where Id in (Select BusinessBackupCustomerId from Rpt_RegistForm where ( SignatureCPA1Id=S.Id or SignatureCPA2Id=S.Id ) and DocStatus=30 ) ) T ) as 'BNum',(case when R.Id is null then 0 else 1 end ) as 'Num', R.ReportBackupDate from Base_Staff S left join Rpt_RegistForm R on ( R.SignatureCPA1Id=S.Id or R.SignatureCPA2Id=S.Id ) and R.DocStatus=30 where S.UserType=3 

This query takes about 10 seconds. After careful analysis, it has two similar result sets (Base_Staff, Rpt_RegistForm associated part), which is the application of CTE.

From SQLSERVER online books, let's take a look at the concept of CET:

Ms-help: // MS. SQLCC. v10/MS. SQLSVR. v10.zh-CHS/s10de_6tsql/html/27cfb819-3e8d-4274-8bbe-cbbe4d9c2e23.htm

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 expression is called a recursive common table expression.

Let's take a look at the CET-modified query:

With CTE as (select -- s. id as S_ID, s. name, s. accountantCode, r. businemediackupcustomerid --, r. id as R_ID, r. signatureCPA1Id, r. signatureCPA2Idfrom Base_Staff S left join Rpt_RegistForm R on (R. signatureCPA1Id = S. id or R. signatureCPA2Id = S. id) and r. docStatus = 30 where s. userType = 3) select t0.*, (Select COUNT (*) from (Select Distinct businease ackupid from biz_businease ackupcustomer B inner join CTE on B. id = CTE. businease ackupcustomerid where t0.AccountantCode = CTE. accountantCode) t1) as 'decision number' from (select Name, AccountantCode, COUNT (businateackupcustomerid) as 'report number' from CTEgroup by Name, AccountantCode) t0

It takes only five seconds to execute this query, Which is doubled from the original query.

Note that the Count function above counts a column. If the value of this column in a row is NULL, the row is not counted, Which is expected.

In addition, CTE can also be used for Recursive processing. For details, see the URL description of the books online.