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

本文给大家介绍使用cte解决复杂查询问题,在此代码中需要注意count函数,它统计了一个列,如果该列在某行的值为null,将不会统计该行,本文代码详解并附有注释,感兴趣的朋友一起看看吧

最近,同事需要从数个表中查询用户的业务和报告数据,写了一个SQL语句,查询比较慢:

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

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改写过的查询:

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

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的内容说明。

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/27054/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022-09-29 4:27:07
下一篇 2022-09-29 4:27:13

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!