乐观主义者从每一个灾难中看到机遇,而悲观主义都从每一个机遇中看到灾难。无论有多困难,都坚强地抬头挺胸,人生是一场醒悟,不要昨日,不要明天,只要今日。
最近,同事需要从数个表中查询用户的业务和报告数据,写了一个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的内容说明。
以上就是在sqlserver中如何如何使用CTE如何解决复杂查询问题。即使行动导致错误,却也带来了学习与成长;不行动则是停滞与萎缩。更多关于在sqlserver中如何如何使用CTE如何解决复杂查询问题请关注haodaima.com其它相关文章!