table
year result
2013 fail
2013 succ
2013 succ
2012 succ
2012 fail
2011 succ
输出
year succ fail
2013 2 1
2012 1 1
2011 1 0
只会写到这里，
而且感觉这个写法不好，不知有没更好的写法
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 [year],
sum(case when result='succ' then 1 else 0 end) as succ,
sum(case when result='fail' then 1 else 0 end) as fail
from [table] group by [year]
------解决方案--------------------
select [year],succ=sum(case when result='succ' then 1 else 0 end),fail=sum(case when result='fail' then 1 else 0 end) from [table] group by [year]
------解决方案--------------------
船长反应真快。
------解决方案--------------------
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