在sqlserver中如何使用CTE解决复杂查询问题
作者:深蓝医生 发布时间:2024-01-24 13:31:34
标签: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
投稿
猜你喜欢
- 1.remove: 删除单个元素,删除首个符合条件的元素,按值删除,从左向右依次删除符合条件的值举例说明:>>> str=
- 准备工作没有加壳的python编译并打包的exe文件python反打包代码(不知道这样叫是否合理):python-exe-unpacker1
- 起步Pandas最初被作为金融数据分析工具而开发出来,因此 pandas 为时间序列分析提供了很好的支持。 Pandas 的名称来自于面板数
- 本文实例讲述了python时间日期操作方法。分享给大家供大家参考,具体如下:#coding=utf-8import timeimport d
- 实验环境:windows 7,anaconda 3(Python 3.5),tensorflow(gpu/cpu)函数介绍:标准化处理可以使
- 平时工作过程中,git在push代码的时候有时会遇到如下的错误错误原因文件冲突,本地的代码和远程Repository中的文件个数不一致(即远
- 使用Django静态设置时,遇到很多问题,经过艰苦的Baidu, stack overflow, Django原档阅读,终于把静态图片给搞出
- 下面请看本文详细介绍.Perl字符串处理函数1. index语法:position=index(string,substring,posit
- model.pyimport datetimefrom django.contrib.auth.models import Userfrom
- 本文实例讲述了Python实现嵌套列表及字典并按某一元素去重复功能。分享给大家供大家参考,具体如下:#! /usr/bin/env pyth
- 前言现在最流行的本地存储莫过于 cookie 的应用,但浏览器对 cookie 有很多限制,最大的限制在于其对cookie 总大小
- 让ASP搭配MYSQL所需要工具mysql-4.1.11-win32 myodbc-3.51.11-1-dll myodbc-3.51.11
- 在了解XHTML代码规范后,我们就要进行CSS布局。首先先介绍一些CSS的入门知识。如果你已经很熟悉了,可以跳过这一节。CSS是Cascad
- ADODB.Stream在ASP编程中往往用于无组件上传,并且可以指定文件编码,可以应对各种不同的需要。但是在使用ADODB.Stream过
- 在之前的博客 人脸识别经典算法一:特征脸方法(Eigenface)里面介绍了特征脸方法的原理,但是并没有对它用到的理论基础PCA做介绍,现在
- csv的简单介绍CSV (Comma Separated Values),即逗号分隔值(也称字符分隔值,因为分隔符可以不是逗号),是一种常用
- 本文介绍的是关于Python利用字典的默认行为的相关内容,分享出来供大家参考学习,下面来看看详细的介绍:典型代码1:from collect
- 假设现在有如下N条记录 表明叫book id author title 1 aaa AAA 2 bbb BBB 3 ccc CCC 4 dd
- 本文实例为大家分享了Python实点云分割k-means(sklearn),供大家参考,具体内容如下植物叶片分割import numpy a
- 现在网页的设计都讲究整体统一风格,无论是网页的文字、图像,还是浏览器的滚动条都要求颜色和风