最近，同事需要从数个表中查询用户的业务和报告数据，写了一个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.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 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 ctegroup by name,accountantcode) t0
执行此查询，只需要5秒钟时间，比原来的查询提高了一倍。
注意上面的count函数，它统计了一个列，如果该列在某行的值为null，将不会统计该行，这正符合需求。
另外，cte还可以做递归处理，详细见上面的联机丛书url的内容说明。