最近，同事需要从数个表中查询用户的业务和报告数据，写了一个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的内容说明。
需要了解更多数据库技术:在sqlserver中如何使用CTE解决复杂查询问题，都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!
本文来自网络收集，不代表计算机技术网立场，如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址：https://www.ctvol.com/dtteaching/613916.html