使用CTE解决复杂查询的问题

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Leetcode名企之路

【Leetcode】175. 组合两个表

编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息:

10010
来自专栏文渊之博

参数化(二):执行查询的方式

前面一篇我介绍了执行计划缓存以及执行之前批处理经过的流程。这篇将用几个最普通的例子介绍查询的几种执行方式。 请看下面这个我使用的这个查询: SELECT Id...

19180
来自专栏Vamei实验室

安卓第七夜 雅典学院

我之前只使用了一种持续保存数据的方法,即SharedPreferences。然而,SharedPreferences只能存储少量松散的数据,并不适合大量数据的存...

23780
来自专栏乐沙弥的世界

MongoDB 部分索引(Partial Indexes)

a、部分索引就是带有过滤条件的索引,即索引只存在与某些文档之上 b、满足过滤条件的文档在查询时,其执行计划将使用该列上的索引,否则不会被使用 c、稀疏索...

15700
来自专栏芋道源码1024

数据库中间件 Sharding-JDBC 源码分析 —— SQL 解析(三)之查询SQL

1. 概述 2. SelectStatement 2.1 AbstractSQLStatement 2.2 SQLToken 3. #query() 3.1 ...

42580
来自专栏me的随笔

T-SQL基础(三)之子查询与表表达式

在嵌套查询中,最外面查询结果集返回给调用方,称为外部查询。嵌套在外部查询内的查询称为子查询,子查询的结果集供外部查询使用。

10810
来自专栏流柯技术学院

MySQL存储过程

SQL语句需要先编译然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的...

29830
来自专栏me的随笔

T-SQL基础(三)之子查询与表表达式

在嵌套查询中,最外面查询结果集返回给调用方,称为外部查询。嵌套在外部查询内的查询称为子查询,子查询的结果集供外部查询使用。

12340
来自专栏大前端_Web

SQL笔记

版权声明:本文为吴孔云博客原创文章,转载请注明出处并带上链接,谢谢。 https://blog.csdn.net/wkyseo/articl...

14020
来自专栏架构师之旅

《干货系列》SQL语句-知无不言言无不尽

1.SQL , Structure Query Language,结构化查询语言,是一种申明式的语言。 SQL包括6部分: 1.DQL(Data Query ...

21350

扫码关注云+社区

领取腾讯云代金券