a1 a2 a3 a4 a5
001 1100 10 23.5 1
002 1100 9 10.1 60
003 1200 17 100 300
004 2200 150 2879.6 6000
005 2100 10 11 空
006 1300 10 23 24
007 1400 15 30 46
008 1400 20 44 101
(a1为编码,a2为种类,a3为数量,a4为金额,a5为天数)(a5为空表示该列数据有可能是0,也有可能是空数据null)
现在要汇总成如下表:
天数 汇总个数 个数占总个数比率 汇总金额 金额占总金额比率
a5<=30
30<a5<=60
60<a5<=90
90<a5<=180
180<a5<=365
a5>365
a5为空
总计
001 1100 10 23.5 1
002 1100 9 10.1 60
003 1200 17 100 300
004 2200 150 2879.6 6000
005 2100 10 11 空
006 1300 10 23 24
007 1400 15 30 46
008 1400 20 44 101
(a1为编码,a2为种类,a3为数量,a4为金额,a5为天数)(a5为空表示该列数据有可能是0,也有可能是空数据null)
现在要汇总成如下表:
天数 汇总个数 个数占总个数比率 汇总金额 金额占总金额比率
a5<=30
30<a5<=60
60<a5<=90
90<a5<=180
180<a5<=365
a5>365
a5为空
总计
-- 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 a5with 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