上面的错了 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,8declare @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,8declare @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 #testA1 B1 C1 10 6 4 60% 40% A1 B1 C2 8 8 0 100% 0%
给加个oracle的 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
select A列,B列,C列,sum(个数) As 总个数,sum(Case when Flag列 = 1 Then 个数 else 0 end) As [Flag=1的个数]
from 表
group by A列,B列,C列
至于另外几列,想减 相除就出来了
(
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,8declare @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 C2 8 8 0 1 0
(
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,8declare @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 #testA1 B1 C1 10 6 4 60% 40%
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以后就提供了交叉表的新特性,可以实现,为什么大家还在用旧办法去实现呢?!