在sqlserver中如何使用CTE解决复杂查询问题

最近，同事需要从数个表中查询用户的业务和报告数据，写了一个sql语句，查询比较慢：

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 

该查询需要执行10秒左右，仔细分析，它有2次查询类似的结果集（base_staff,rpt_registform 关联部分），这正是cte应用的场合。

从sqlserver 联机丛书，我们来了解下cet的概念：

ms-help://ms.sqlcc.v10/ms.sqlsvr.v10.zh-chs/s10de_6tsql/html/27cfb819-3e8d-4274-8bbe-cbbe4d9c2e23.htm

指定临时命名的结果集，这些结果集称为公用表表达式 (cte)。该表达式源自简单查询，并且在单条 select、insert、update、merge 或 delete 语句的执行范围内定义。该子句也可用在 create view 语句中，作为该语句的 select 定义语句的一部分。公用表表达式可以包括对自身的引用。这种表达式称为递归公用表表达式。

下面看看经过cet改写过的查询：

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 '约定书数'
from 
(
select name, accountantcode,count( businessbackupcustomerid) as '报告数'
from cte
group by name,accountantcode
) t0

执行此查询，只需要5秒钟时间，比原来的查询提高了一倍。

注意上面的count函数，它统计了一个列，如果该列在某行的值为null，将不会统计该行，这正符合需求。

另外，cte还可以做递归处理，详细见上面的联机丛书url的内容说明。