在sqlserver中如何使用CTE解决复杂查询问题
作者:深蓝医生 时间:2024-01-24 13:31:34
最近,同事需要从数个表中查询用户的业务和报告数据,写了一个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,复杂查询
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
python process模块的使用简介
2023-08-05 13:13:13
python实现手机销售管理系统
2023-01-16 00:02:04
![](https://img.aspxhome.com/file/2023/3/81713_0s.jpg)
Golang 分割字符串的实现示例
2024-02-23 03:26:10
写给JavaScript库开发者们的规则
2008-10-26 12:30:00
Python3中的re.findall()方法及re.compile()
2023-04-12 11:54:47
10行Python代码助你整理杂乱无章的文件
2021-03-08 21:11:12
Python ORM框架之SQLAlchemy 的基础用法
2023-06-09 18:03:41
![](https://img.aspxhome.com/file/2023/1/131151_0s.png)
python学生管理系统代码实现
2023-10-31 07:55:04
PyTorch中topk函数的用法详解
2022-11-08 13:55:52
![](https://img.aspxhome.com/file/2023/3/86183_0s.jpg)
sql 查询本年、本月、本日记录的语句,附SQL日期函数
2024-01-25 01:00:55
sql server 编译与重编译详解
2024-01-14 11:02:59
多种还原.bak数据库文件方式
2008-02-25 13:51:00
mysql 5.7.18 免安装版window配置方法
2024-01-14 15:03:45
![](https://img.aspxhome.com/file/2023/4/75574_0s.jpg)
浅析Python 中整型对象存储的位置
2021-10-06 13:40:20
给年青设计师们的十条经验教训
2011-03-31 17:09:00
jsp/javascript打印九九乘法表代码
2024-03-23 02:23:17
ASP页面内VBScript和JScript的交互
2007-09-11 13:49:00
使用python对视频文件分辨率进行分组的实例代码
2022-06-06 21:16:44
springboot多数据源配合docker部署mysql主从实现读写分离效果
2024-01-28 11:14:53
![](https://img.aspxhome.com/file/2023/6/110836_0s.jpg)
彻底解决Python包下载慢问题
2021-07-17 16:02:27
![](https://img.aspxhome.com/file/2023/3/115093_0s.png)