欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-05-03 07:56:16
最近,同事需要从数个表中查询用户的业务和报告数据,写了一个sql语句,查询比较慢: select s.name, s.accountantcode, (...

最近,同事需要从数个表中查询用户的业务和报告数据,写了一个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的内容说明。