SQL Server 2008 R2 中的查询优化 [英] Query Optimisation in SQL Server 2008 R2

问题描述

我知道这里经常有人问这个问题，但我有一些不同的要求.

I know this question is asked frequently here but I have some different requirements.

我想得到这个输出.我的逻辑也得到了这一点，但这可以以更简单和优化的方式完成.更快地运行此查询.

I want to get this output. I am getting this also with my logic but can this can be done in more simple and optimised way. To run this query faster.

这是我的查询:

select 
    ISNULL('PortFolio Code: '+a.CPORTFOLIOCODE,'Grand Total') as [PortFolio Code],
    COUNT(SZCUSTOMERNO) as [Accounts],
    CAST(COUNT(SZCUSTOMERNO) * 100 / (Select COUNT(SZCUSTOMERNO) 
                                      from dbo.COL_TRN_AGREEMENT) 
                                      as nvarchar(50))+' %' as [%],
    sum(case when a.SZBUCKETCODE =1 then a.FOSAMT else 0 end ) as [Bucket :1],
    sum(case when a.SZBUCKETCODE =2 then a.FOSAMT else 0 end ) as [Bucket :2],
    sum(case when a.SZBUCKETCODE =3 then a.FOSAMT else 0 end ) as [Bucket :3],
    sum(a.FOSAMT) as [All Buckets] 
from 
    dbo.COL_TRN_AGREEMENT a 
group by 
    a.CPORTFOLIOCODE with rollup

我得到这个输出

这是否可以使用具有更快执行速度的简单逻辑来完成，或者这是最简单的方法.

Can this be done using simple logic with faster execution or this is the simplest way.

推荐答案

这里有一个方法，可能不是更快，但您仍然可以进行比较.

Here is an approach, may not be faster, but you still have a choise to compare.

declare @cnt int
Select @cnt = COUNT(SZCUSTOMERNO) from dbo.COL_TRN_AGREEMENT

;with 
matrix (SZBUCKETCODE,Bucket_1,Bucket_2,Bucket_3) as 
       (select 1,1,0,0 union
        select 2,0,1,0 union
        select 3,0,0,1)
select isnull('PortFolio Code: '+a.CPORTFOLIOCODE,'Grand Total') as [PortFolio Code],
       COUNT(SZCUSTOMERNO) as [Accounts],
       cast(COUNT(SZCUSTOMERNO)*100/@cnt as nvarchar(50))+' %' as [%],
       sum(a.FOSAMT*m.Bucket_1) as [Bucket :1],
       sum(a.FOSAMT*m.Bucket_2) as [Bucket :2],
       sum(a.FOSAMT*m.Bucket_3) as [Bucket :3],
       sum(a.FOSAMT) as [All Buckets] 
  from dbo.COL_TRN_AGREEMENT a 
  join matrix m
    on m.SZBUCKETCODE = a.SZBUCKETCODE 
 group by a.CPORTFOLIOCODE with rollup

这篇关于SQL Server 2008 R2 中的查询优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！