SQL达人进

newFishInTheSea 2010-08-20 05:13:10

表结构：
A列 B列 C列 Flag列个数
A1 B1 C1 0 1
A1 B1 C1 0 5
A1 B1 C1 1 4
A1 B1 C2 0 8
。。。

有上述的表结构，现在需要汇总：按照A列、B列、C列汇总个数，大致如下：
A列 B列 C列总个数 Flag=0的个数 Flag=1的个数 Flag=0的个数百分比 Flag=1的个数百分比
A1 B1 C1 10 6 4 60% 40%
A1 B1 C2 8 8 0 100% 0%

需要怎么实现？
求救达人！！！

...全文

125 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

noobw 2010-08-24

打赏
举报

学习了！

liwenguang_2009 2010-08-20

打赏
举报

create table #test

(

    A NVARCHAR(10),

    B NVARCHAR(10),

    C NVARCHAR(10),

    Flag INT,

    Countity INT

)



INSERT INTO #test

select 'A1','B1','C1',0,1

union all

select 'A1','B1','C1',0,5

union all

select 'A1','B1','C1',1,4

union all

select 'A1','B1','C2',0,8



select A,B,C,sum(Countity) as 总个数,

        sum(case Flag when 0 then Countity else 0 end ) as [Flag=0的个数],

        sum(case Flag when 1 then Countity else 0 end ) as [Flag=1的个数],

        str(sum(case Flag when 0 then Countity else 0 end )*100/cast(sum(Countity) as float))+'%' as [Flag=0的个数百分比],

        str(sum(case Flag when 1 then Countity else 0 end )*100/cast(sum(Countity) as float))+'%' as [Flag=1的个数百分比]

from #test

group by A,B,C

drop table #test

A	B	C	总个数	Flag=0的个数	Flag=1的个数	Flag=0的个数百分比	Flag=1的个数百分比

A1	B1	C1	10	6	4	        60%	        40%

A1	B1	C2	8	8	0	       100%	         0%

幸运的意外 2010-08-20

打赏
举报

我现在怎么连要求都看不懂了。退化了。

Johnson 2010-08-20

打赏
举报

[Quote=引用 2 楼 hao1hao2hao3 的回复:]
错了

应该是

SQL code

select a,b,c,sum(cou) as '总数',sum(case when flag =0 then cou else 0 end) as 'flag0的个数',sum(case when flag=1 then cou else 0 end) as 'flag1的个数',sum(case when flag =0 then cou……
[/Quote]UP

hao1hao2hao3 2010-08-20

打赏
举报

错了

应该是



select a,b,c,sum(cou) as '总数',sum(case when flag =0 then cou else 0 end) as 'flag0的个数',sum(case when flag=1 then cou else 0 end) as 'flag1的个数',sum(case when flag =0 then cou else 0 end)*1.0/sum(cou) as '0比例',

sum(case when flag=1 then cou else 0 end) *1.0/sum(cou) as '1比例'

from tb

group by a,b,c





结果



A1	B1	C1	10	6	4	0.600000000000	0.400000000000

A1	B1	C2	8	8	0	1.000000000000	0.000000000000

hao1hao2hao3 2010-08-20

打赏
举报



if object_id('tb')>0

	drop table tb

create table tb 

(

	a varchar(5),

	b varchar(5),

	c varchar(5),

	flag varchar(1),

	cou int

)



select * from tb



insert into tb

select 'A1','B1','C1','0',1

union all

select 'A1','B1','C1','0',5

union all

select 'A1','B1','C1','1',4

union all

select 'A1','B1','C2','0',8



select a,b,c,sum(cou) as '总数',sum(case when flag =0 then 1 else 0 end) as 'flag0的个数',sum(case when flag=1 then 1 else 0 end) as 'flag1的个数',sum(case when flag =0 then 1 else 0 end)*0.1/sum(cou) as '0比例',

sum(case when flag=1 then 1 else 0 end) *0.1/sum(cou) as '1比例'

from tb

group by a,b,c





结果



A1	B1	C1	10	2	1	0.020000000000	0.010000000000

A1	B1	C2	8	1	0	0.012500000000	0.000000000000