SQL达人进
表结构: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%
需要怎么实现?
求救达人!!!
--------------------编程问答-------------------- 需要如何统计呢? --------------------编程问答-------------------- --------------------编程问答-------------------- --------------------编程问答-------------------- select A列,B列,C列,sum(个数) As 总个数,sum(Flag列) As [Flag=1的个数]
from 表
group by A列,B列,C列 --------------------编程问答-------------------- 上面的错了
select A列,B列,C列,sum(个数) As 总个数,sum(Case when Flag列 = 1 Then 个数 else 0 end) As [Flag=1的个数]
from 表
group by A列,B列,C列
至于另外几列,想减 相除就出来了 --------------------编程问答--------------------
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
declare @total int
declare @zeroCount int
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的个数],
sum(case Flag when 0 then Countity else 0 end )/cast(sum(Countity) as float) as [Flag=0的个数百分比],
sum(case Flag when 1 then Countity else 0 end )/cast(sum(Countity) as float) as [Flag=1的个数百分比]
from #test
group by A,B,C
drop table #test
A1 B1 C1 10 6 4 0.6 0.4--------------------编程问答--------------------
A1 B1 C2 8 8 0 1 0
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
declare @total int
declare @zeroCount int
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
A1 B1 C1 10 6 4 60% 40%--------------------编程问答-------------------- 给加个oracle的
A1 B1 C2 8 8 0 100% 0%
--------------------编程问答--------------------
SELECT DISTINCT A
,B
,C
,SUM(N) OVER (PARTITION BY A, B, C)
,SUM(DECODE(F, 0, 0, N) ) OVER (PARTITION BY A, B, C)
,SUM(DECODE(F, 1, 0, N) ) OVER (PARTITION BY A, B, C)
FROM T
顶 --------------------编程问答-------------------- 我的天啊,
微软MSSQL 2005以后就提供了
交叉表的新特性,可以实现,为什么大家还在用旧办法去实现呢?!
补充:.NET技术 , C#