/*
-- Author:Flystone
-- Date:2008-05-15
-- Version:V1.001
*/
-- Test Data: Ta
If object_id('Ta') is not null
Drop table Ta
Go
Create table Ta(a1 int,a2 int,a3 int,a4 numeric(8,1),a5 int)
Go
Insert into Ta
select 001,1100,10,23.5,1 union all
select 002,1100,9,10.1,60 union all
select 003,1200,17,100,300 union all
select 004,2200,150,2879.6,6000 union all
select 005,2100,10,11,null union all
select 006,1300,10,23,24 union all
select 007,1400,15,30,46 union all
select 008,1400,20,44,101
Go
--Start
select isnull(a5,'总计') as [天数] ,sum(a3) as 汇总个数,cast(sum(a3)*100.0/(select sum(a3) from ta) as numeric(5,2)) as 个数占总个数比率,
sum(a4) as 汇总金额,cast(sum(a4)*100.0/(select sum(a4) from ta)as numeric(5,2)) as 金额占总金额比率
from (
Select a1,a2,a3,a4,a5 = case when a5 is null then 'a5为空'
when a5 > 365 then ' > 365 '
when a5 between 180 and 365 then '180-365'
when a5 between 90 and 180 then '90- 180'
when a5 between 60 and 90 then '60 - 90'
when a5 between 30 and 60 then '30 - 60'
when a5 <= 30 then ' < 30 ' end
from Ta) a
group by a5
with cube
--Result:
/*
天数 汇总个数 个数占总个数比率 汇总金额 金额占总金额比率
------- ----------- -------- ---------------------------------------- --------
<30 20 8.30 46.5 1.49
>365 150 62.24 2879.6 92.26
180-365 17 7.05 100.0 3.20
30 - 60 15 6.22 30.0 .96
60 - 90 9 3.73 10.1 .32
90- 180 20 8.30 44.0 1.41
a5为空 10 4.15 11.0 .35
总计 241 100.00 3121.2 100.00
（所影响的行数为 8 行）
*/
--End
