日期:2014-05-17 浏览次数:20709 次
select rybh,ryxm,
'赞成',count(case when tpjg='赞成' then 1 else null end),
'反对',count(case when tpjg='反对' then 1 else null end),
'弃权',count(case when tpjg='弃权' then 1 else null end)
from 表
group by rybh,ryxm
select rybh,ryxm,
'赞成'+cast(sum(case when tpjg='赞成' then 1 else 0 end) as varchar(10)) as '赞成票数',
'反对'+cast(sum(case when tpjg='反对' then 1 else 0 end) as varchar(10)) as '反对票数',
'弃权'+cast(sum(case when tpjg='弃权' then 1 else 0 end) as varchar(10)) as '弃权票数'
from tb group by rybh,ryxm
--换个转换的写法
select rybh,ryxm,
'赞成'+convert(nvarchar(10),sum(case when tpjg='赞成' then 1 else 0 end)) as '赞成票数',
'反对'+convert(nvarchar(10),sum(case when tpjg='反对' then 1 else 0 end)) as '反对票数',
'弃权'+convert(nvarchar(10),sum(case when tpjg='弃权' then 1 else 0 end)) as '弃权票数'
from tb
group by rybh,ryxm
select rybh,ryxm,'赞成',
(select count(1) from revote where tpjg='赞成' and rybh=a.rybh ) ,
'反对',(select count(1) from revote where tpjg='反对' and rybh=a.rybh ) ,
'弃权',(select count(1) from revote where tpjg='弃权' and rybh=a.rybh )
from revote a group by rybh,ryxm