select a.serviceID,a.servicetype,
sum(case b.Isok when 1 then 1 else 0 end) ok,
sum(case b.Isok when 0 then 1 else 0 end) un,
sum(case when b.Isok=0 or b.Isok=1 then 1 else 0 end) [count],
sum(case b.Isok when 1 then 1 else 0 end)/sum(case when b.Isok=0 or b.Isok=1 then 1 else 0 end) 'ok/count',
sum(case b.Isok when 0 then 1 else 0 end)/sum(case when b.Isok=0 or b.Isok=1 then 1 else 0 end) 'unok/count'
from tbservicetype a left join tbservice b on a.serviceid=b.serviceid
group by a.serviceID,a.servicetype
sum(case b.Isok when 1 then 1 else 0 end) ok,
sum(case b.Isok when 0 then 1 else 0 end) un,
sum(case when b.Isok=0 or b.Isok=1 then 1 else 0 end) [count],
sum(case b.Isok when 1 then 1 else 0 end)/sum(case when b.Isok=0 or b.Isok=1 then 1 else 0 end) 'ok/count',
sum(case b.Isok when 0 then 1 else 0 end)/sum(case when b.Isok=0 or b.Isok=1 then 1 else 0 end) 'unok/count'
from tbservicetype a left join tbservice b on a.serviceid=b.serviceid
group by a.serviceID,a.servicetype
解决方案 »
- 怎么删除SQL多个表中2008-1-1前的数据
- 又是多表查询的问题
- |zyciis| 晕 我SQL白痴,又记了怎么限定UserName表不能出现相同,谢谢 第三贴
- 如何在SQL中取消运行时间过长的查询(新手提问,盼高手回答)
- SQL2005无法连接
- 求教大师一句sql
- 安装SP3,提示“指定的实例名称无效”为什么?
- SQL语句这是什么意思 tempdb..sysobjects
- 在从DataGrid修改数据时,怎么样能同步更新数据库中内容!!
- 救命啊!!为什么在DBASE数据库里执行“ALTER TABLE TableName ADD COLUMN AAA C(10)”发生异常?!,使用环境:VC++,该语句不兼容啊!
- 请问,一个简单的SQL语句
- ---非常菜的问题:用DSN连接并且有用户名和密码
select a.serviceID,a.servicetype,
sum(case b.Isok when 1 then 1 else 0 end) ok,
sum(case b.Isok when 0 then 1 else 0 end) unok,
sum(case when b.Isok=0 or b.Isok=1 then 1 else 0 end) [count],
case sum(case b.Isok when 1 then 1 else 0 end)
when 0 then 0
else sum(case b.Isok when 1 then 1 else 0 end)
/sum(case when b.Isok=0 or b.Isok=1 then 1 else 0 end)
end 'ok/count',
case sum(case b.Isok when 0 then 1 else 0 end)
when 0 then 0
else sum(case b.Isok when 0 then 1 else 0 end)
/sum(case when b.Isok=0 or b.Isok=1 then 1 else 0 end)
end 'unok/count'
from tbservicetype a left join tbservice b on a.serviceid=b.serviceid
group by a.serviceID,a.servicetype
select a.serviceID,a.servicetype,
sum(case b.Isok when 1 then 1 else 0 end) ok,
sum(case b.Isok when 0 then 1 else 0 end) unok,
sum(1) [count],
case sum(1) when 0 then 0
else cast(cast(
sum(case b.Isok when 1 then 100.0 else 0.0 end)
/sum(1) as decimal(10,2)) as varchar)+'%'
end as [ok/count],
case sum(1) when 0 then 0
else cast(cast(
sum(case b.Isok when 0 then 100.0 else 0.0 end)
/sum(1) as decimal(10,2)) as varchar)+'%'
end as [unok/count],
case (select count(*) from tbservice) when 0 then 0
else cast(cast(
sum(100.0)
/(select count(*) from tbservice)
as decimal(10,2)) as varchar)+'%'
end as [count/allcount]
from tbservicetype a
left join tbservice b on a.serviceid=b.serviceid
group by a.serviceID,a.servicetype
select a.serviceID,a.servicetype,
sum(case b.Isok when 1 then 1 else 0 end) ok,
sum(case b.Isok when 0 then 1 else 0 end) unok,
sum(1) [count],
case sum(1) when 0 then 0
else cast(cast(
sum(case b.Isok when 1 then 100.0 else 0.0 end)
/sum(1) as decimal(10,2)) as varchar)+'%'
end as [ok/count],
case sum(1) when 0 then 0
else cast(cast(
sum(case b.Isok when 0 then 100.0 else 0.0 end)
/sum(1) as decimal(10,2)) as varchar)+'%'
end as [unok/count],
case isnull(c.allcount,0) when 0 then 0
else cast(cast(
sum(100.0)
/c.allcount
as decimal(10,2)) as varchar)+'%'
end as [count/allcount]
from tbservicetype a
left join tbservice b on a.serviceid=b.serviceid
,(select allcount=count(*) from tbservice) c
group by a.serviceID,a.servicetype
select a.serviceID,a.servicetype,
sum(case b.Isok when 1 then 1 else 0 end) ok,
sum(case b.Isok when 0 then 1 else 0 end) unok,
sum(case when b.Isok=0 or b.Isok=1 then 1 else 0 end) [count],
case sum(case b.Isok when 1 then 1 else 0 end)
when 0 then 0
else sum(case b.Isok when 1 then 1 else 0 end)
/sum(case when b.Isok=0 or b.Isok=1 then 1 else 0 end)
end 'ok/count',
case sum(case b.Isok when 0 then 1 else 0 end)
when 0 then 0
else sum(case b.Isok when 0 then 1 else 0 end)
/sum(case when b.Isok=0 or b.Isok=1 then 1 else 0 end)
end 'unok/count',
case sum(case when b.Isok=0 or b.Isok=1 then 1 else 0 end)
when 0 then 0
else sum(case when b.Isok=0 or b.Isok=1 then 1 else 0 end)
/(select count(*) from serviceID)
end 'count/allcount'
from tbservicetype a left join tbservice b on a.serviceid=b.serviceid
group by a.serviceID,a.servicetype