sql语句请教，在线等待

[Luyb111]

SELECT BMRQ,
SUM( CASE WHEN yysj>=8 AND yysj<=16 AND Isnull(csny,0) = 1 AND kzbz = '已登录' THEN 1 ELSE 0 END ) as a,
SUM( CASE WHEN yysj>=8 AND yysj<=16 AND (Isnull(csny,0) <> 1 OR kzbz <> '已登录') THEN 1 ELSE 0 END ) as b,
SUM( CASE WHEN yysj>=8 AND yysj<=16 AND Isnull(csny,0) = 1 AND kzbz = '已登录' AND ZCJ>=60 THEN 1 ELSE 0 END )as c

FROM BmInf
WHERE BMRQ >='2005-01-19' AND BMRQ <='2006-01-20'
GROUP BY BMRQ

order by bmrq
其中显示数据如下：
BMRQ a b c
2005-1-1 79 1 75
2005-1-2 0 0 0
2005-1-3 8 0 7
...
...


BMRQ：日期
a:实考人数
b:缺考人数
c:合格人数
我现在要加一个字段d：为合格率= c/a
现在碰到一个问题如果a=0，SQL就会包错
我要求如果a = 0时候 c显示为'/'
当a<> 0就显示c/a的值
不能在GROUP BY BMRQ
语句后面加上一个having语句把a=0的记录过滤掉

[huailairen]

SELECT BMRQ,
SUM( CASE WHEN yysj>=8 AND yysj<=16 AND Isnull(csny,0) = 1 AND kzbz = '已登录' THEN 1 ELSE 0 END ) as a,
SUM( CASE WHEN yysj>=8 AND yysj<=16 AND (Isnull(csny,0) <> 1 OR kzbz <> '已登录') THEN 1 ELSE 0 END ) as b,
SUM( CASE WHEN yysj>=8 AND yysj<=16 AND Isnull(csny,0) = 1 AND kzbz = '已登录' AND ZCJ>=60 THEN 1 ELSE 0 END )as c
d=(case SUM( CASE WHEN yysj>=8 AND yysj<=16 AND Isnull(csny,0) = 1 AND kzbz = '已登录' THEN 1 ELSE 0 END ) when 0 then '/' else cast(SUM( CASE WHEN yysj>=8 AND yysj<=16 AND Isnull(csny,0) = 1 AND kzbz = '已登录' AND ZCJ>=60 THEN 1 ELSE 0 END ))+'/'+ cast(SUM( CASE WHEN yysj>=8 AND yysj<=16 AND Isnull(csny,0) = 1 AND kzbz = '已登录' THEN 1 ELSE 0 END ) as varchar(30)) end)

FROM BmInf
WHERE BMRQ >='2005-01-19' AND BMRQ <='2006-01-20'
GROUP BY BMRQ

[Luyb111]

非常感谢huailairen(流浪猫）
你的方法我发贴前已经试过，不行

[whulph]

huailairen(流浪猫)是不是漏写了一部分内容，导致有语法错误？

可以先得到，存入临时表（@temptable）
BMRQ a b c
2005-1-1 79 1 75
2005-1-2 0 0 0
2005-1-3 8 0 7
再在这个基础上执行
select *, d = (case a when 0 then '/' else cast(cast(c as float)/a as varchar(10)) end) from @temptable

上面的我试了，是可以的
关键是cast(cast(c as float)/a as varchar(10)) 这里将cast(c as float)/a转换为字符时，如果varchar位数太少将导致溢出：发生类型 varchar 的算术溢出错误

[Luyb111]

刚才有个地方写错了“我要求如果a = 0时候 C显示为'/'”更正如下“我要求如果a = 0时候 D显示为'/'”