在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
投稿
猜你喜欢
- 闭包函数什么是闭包函数如果内函数使用了外函数的局部变量,并且外函数把内函数返回出来的过程叫做闭包,里面的内函数是闭包函数。# 外函数 out
- 简介由于项目在注册、登录、找回密码 时需要发送短信验证的功能,我们使用腾讯云短信做。为什么要用腾讯云短信呢? 因为注册就送 100条免费短信
- response.getWriter().write() 功能:向前台页面显示一段信息。当在普通的url方式中,会生成一个新的页面来显示内容
- 直接进入正题解析字符串对象我们都知道,JavaScript对象可以序列化为JSON,JSON也可以解析成对象,但是问题是如果出现了一个既不是
- 导言Python官方文档对于内置函数的介绍较为简略,但这些内置函数在日常工作中却扮演着不可或缺的角色。为了更加便捷地使用和查阅这些函数,笔者
- 牛顿法求平方根原理计算机常用循环来计算F的平方根.从某个猜测的x值开始,根据x^2与F的近似度来调整x,产生一个更好的猜测:x -= (x
- 1、登录SMTP服务器首先使用网上的方法(这里使用163邮箱,smtp.163.com是smtp服务器地址,25为端口号):import s
- SQL Server PRIMARY KEY(主键)约束简介主键是唯一标识表中每一行的一列或一组列。您可以使用主键约束为表创建主键。如果主键
- 简单介绍正则表达式并不是Python的一部分。正则表达式是用于处理字符串的强大工具,拥有自己独特的语法以及一个独立的处理引擎,效率上可能不如
- MySQLWorkbench是 MySQL AB 最近释放的可视数据库设计工具。这个工具是设计 MySQL数据库的专用工具。下载地址:htt
- 前言孙悟空在花果山称王的时候,特意去了一趟东海,在那里淘到了如意金箍棒。因为身为一个山大王,怎么能没有一件趁手的兵器呢?作为程序员的我们也一
- 一个几百行代码做出http/https代理服务器的脚本,启动即可做http https透明代理使用python proxy.py 8992使
- 概述基于Swoole的websocket服务,计划整合3篇进行技术整理,该服务主要有2个核心业务,用户消息服务(消息计数统计)和 客服IM消
- Python的标准库中的os模块包含普遍的操作系统功能。如果你希望你的程序能够与平台无关的话,这个模块是尤为重要的。即它允许一个程序在编写后
- 在实际的工作和学习中,许多人的SQL Server 2005数据库日志文件可能会发生损坏,例如硬件故障、计算机非正常重启或关机等等。在SQL
- Chrome的CSS支持程度 :Green / √ means current support.Orange / Δ means that
- 任务描述本次实践是一个多分类任务,需要将照片中的宝石分别进行识别,完成宝石的识别实践平台:百度AI实训平台-AI Studio、Paddle
- 企业管理器中没有改数据库名的功能,如果一定要用企业管理器来实现,你可以备份数据库,然后还原,在还原时候可以指定另一个库名,然后再删除旧库就行
- 到2019年初,Python3已经更新到了Python
- 作者认为最快的学习区块链的方式是自己创建一个,本文就跟随作者用Python来创建一个区块链。对数字货币的崛起感到新奇的我们,并且想知道其背后