How to use a CTE to solve complex query problems in SQL Server _mssql

Recently, colleagues need to query the user's business and report data from several tables, write a SQL statement, the query 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 are 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 

The query needs to perform about 10 seconds, carefully analyzed, and it has 2 queries for similar result sets (Base_staff,rpt_registform associations), which is where CTE is applied.

From SQL Server Books Online, 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 temporary named result set, which is called a common table expression (CTE). The expression is derived from a simple query and is defined within the execution scope of a single SELECT, INSERT, UPDATE, MERGE, or DELETE statement. The clause can also be used in the CREATE VIEW statement as part of the SELECT definition statement for the statement. A common table expression can include a reference to itself. This type of expression is called a recursive common table expression.

Here's a look at the CET rewrite query:

With CTE as
(
select 
    --s.id as s_id,
    s.name, S.accountantcode,
    R.businessbackupcustomerid--, R. Id as r_id, R.signaturecpa1id,r.signaturecpa2id
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 
)
select T0. *
, (
 select COUNT (*) from (
  select Distinct businessbackupid from 
  Biz_businessbackupcustomer b
  INNER join CTE on b.ID =cte. Businessbackupcustomerid
  where t0. Accountantcode=cte. Accountantcode
) T1
) as ' agreed book '
from (select Name, Accountantcode,count ( Businessbackupcustomerid) as ' reports ' from
CTE
Group by Name,accountantcode
T0

It takes only 5 seconds to execute this query, which is one-fold higher than the original query.

Note The Count function above, which counts a column, and if the column has a null value in a row, the row will not be counted, which is in line with the requirement.

In addition, the CTE can also be done recursively, in detail, see the contents of the online books URL above.