select name,case (查询条件 count(*)) from table) when 0 then 0 else (查条件集合 count(*)) / (查询条件 count(*)) end from table
declare @t table(a int,b int) insert @t select 10,5 union all select 2,0select case b when 0 then 0 else a/b end from @t /* ----------- 2 0(2 行受影响) */
select name,case when (查询条件1 count(*))=0 then 0 else (查条件集合 count(*)) / (查询条件1 count(*)) end from table
select name,sum( case when 查条件集合 then 1 else 0 end)/ sum(case when 查询条件 then 1 else 0 end) from table --注意遇到被零除错误 ,加个判断
回1楼和3楼.在运行过程中还是有除零错误.小弟在SQL这一块比较弱,希望各位高手帮帮忙谢谢. 我的查询语句: select pname, case when (select count(*) from baseinfo as a where a.pname=b.pname and(jjgz='1' or jjgz='2'))=0 then 0 else (select count(*) from baseinfo as a where a.pname=b.pname and(jjgz='1' or jjgz='2')) / (select count(*) from baseinfo as a where a.pname=b.pname and jjgz!='1'and jjgz!='2' and (jjgz='3' or jjgz is null)) end from baseinfo as b
回4楼: 小弟的SQL不行.希望能给一个具体点的查询方法例子.谢谢
select pname, case when (select count(*) from baseinfo as a where a.pname=b.pname and jjgz!='1'and jjgz!='2' and (jjgz='3' or jjgz is null))=0 then 0 else (select count(*) from baseinfo as a where a.pname=b.pname and(jjgz='1' or jjgz='2')) / (select count(*) from baseinfo as a where a.pname=b.pname and jjgz!='1'and jjgz!='2' and (jjgz='3' or jjgz is null)) end from baseinfo as b你选错个了,改为这样。
select pname, case when (select count(*) from baseinfo as a where a.pname=b.pname and jjgz!='1'and jjgz!='2' and (jjgz='3' or jjgz is null))=0 then 0 else (select count(*) from baseinfo as a where a.pname=b.pname and(jjgz='1' or jjgz='2')) / (select count(*) from baseinfo as a where a.pname=b.pname and jjgz!='1'and jjgz!='2' and (jjgz='3' or jjgz is null)) end from baseinfo as b
insert @t select 10,5
union all select 2,0select case b when 0 then 0 else a/b end from @t
/*
-----------
2
0(2 行受影响)
*/
--注意遇到被零除错误 ,加个判断
我的查询语句:
select pname,
case when
(select count(*) from baseinfo as a where a.pname=b.pname and(jjgz='1' or jjgz='2'))=0
then 0
else
(select count(*) from baseinfo as a where a.pname=b.pname and(jjgz='1' or jjgz='2')) /
(select count(*) from baseinfo as a where a.pname=b.pname and jjgz!='1'and jjgz!='2' and (jjgz='3' or jjgz is null))
end from baseinfo as b
小弟的SQL不行.希望能给一个具体点的查询方法例子.谢谢
case when
(select count(*) from baseinfo as a where a.pname=b.pname and jjgz!='1'and jjgz!='2' and (jjgz='3' or jjgz is null))=0
then 0
else
(select count(*) from baseinfo as a where a.pname=b.pname and(jjgz='1' or jjgz='2')) /
(select count(*) from baseinfo as a where a.pname=b.pname and jjgz!='1'and jjgz!='2' and (jjgz='3' or jjgz is null))
end from baseinfo as b你选错个了,改为这样。
回4楼:
小弟的SQL不行.希望能给一个具体点的查询方法例子.谢谢你刚才改的方法正确,只是选择了被除数来判断,应该选择除数来作是非0的判断。
case when
(select count(*) from baseinfo as a where a.pname=b.pname and jjgz!='1'and jjgz!='2' and (jjgz='3' or jjgz is null))=0
then 0
else
(select count(*) from baseinfo as a where a.pname=b.pname and(jjgz='1' or jjgz='2')) /
(select count(*) from baseinfo as a where a.pname=b.pname and jjgz!='1'and jjgz!='2' and (jjgz='3' or jjgz is null))
end
from baseinfo as b
2L的方法不可以么