SQL Server 2008 R2中的查询优化

Question

我知道这个问题在这里经常被问到，但我有一些不同的要求。

我想得到这个输出。我也在用我的逻辑解决这个问题，但是这可以通过更简单和优化的方式来完成。更快地运行此查询。

这里是我的查询：

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 

我得到这个输出

可以这样用简单的逻辑和更快的执行完成，或者这是最简单的方法。

Answer 1

以下是一种方法，可能不会更快，但您仍然可以选择进行比较。

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