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

站长资源 2024/12/26 佚名

2 0 1

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

cte解决复杂查询,如何解决复杂问题

广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！

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

暂无评论...

P70系列延期，华为新旗舰将在下月发布

3月20日消息，近期博主@数码闲聊站透露，原定三月份发布的华为新旗舰P70系列延期发布，预计4月份上市。

而博主@定焦数码爆料，华为的P70系列在定位上已经超过了Mate60，成为了重要的旗舰系列之一。它肩负着重返影像领域顶尖的使命。那么这次P70会带来哪些令人惊艳的创新呢？
根据目前爆料的消息来看，华为P70系列将推出三个版本，其中P70和P70 Pro采用了三角形的摄像头模组设计，而P70 Art则采用了与上一代P60 Art相似的不规则形状设计。这样的外观是否好看见仁见智，但辨识度绝对拉满。

更新日志

2024年12月26日

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

Sql存储过程游标循环的用法及sql如何使用cursor写一个简单的循环

针对distinct疑问引发的一系列思考

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

P70系列延期，华为新旗舰将在下月发布

更新日志

友情链接