日期:2014-05-17 浏览次数:20857 次
select * from
(select year,count(*) succ from table where result='succ' group by year) s,
(select year,count(*) fail from table where result='fail' group by year) f
where s.year=f.year;
select year,
max(case result when 'succ' then isnull(num,0) end) succ,
max(case result when 'fail' then isnull(num,0) end) fail
from
(
select year,result,count(1) num from tb
group by year,result
) t
group by year
order by year desc
/*
year,succ,fail
2013,2,1
2012,1,1
2011,1,NULL
警告: 聚合或其他 SET 操作消除了空值。
(3 行受影响)
select year,
isnull(max(case result when 'succ' then isnull(num,0) end),0) succ,
isnull(max(case result when 'fail' then isnull(num,0) end),0) fail
from
(
select year,result,count(1) num from tb
group by year,result
) t
group by year
order by year desc
/*
year,succ,fail
2013,2,1
2012,1,1
2011,1,0
警告: 聚合或其他 SET 操作消除了空值。
(3 行受影响)
select year1,
sum(case result when 'succ' then 1 else 0 end) succ,
sum(case result when 'fail' then 1 else 0 end) fail
from #c
group by year1