Question

sql 为什么两列相除 结果都是0

SELECT KQ AS 考场, ZYMC AS 专业名称, SUM(CASE WHEN ttmis IN ('a', 'b', 'c', 'd') THEN 1 ELSE 0 END) AS 错误总人数, SUM(CASE WHEN cldm IN ('01', '02', '03') THEN 1 ELSE 0 END) AS 违纪总人数, SUM(CASE WHEN pass_1 = '201405' THEN 1 ELSE 0 END) AS 合格人数, SUM(CASE WHEN score_1 IN ('1', '-2', '-3', '-4', '-5') THEN 1 ELSE 0 END) AS 实考人数, SUM(CASE WHEN score_1 IN ('1', '-1', '-2', '-3', '-4', '-5') THEN 1 ELSE 0 END) AS 应考人数, CONVERT(decimal(10, 2), SUM(CASE WHEN pass_1 = '201405' THEN 1 ELSE 0 END) / SUM(CASE WHEN score_1 IN ('1', '-1', '-2', '-3', '-4', '-5') THEN 1 ELSE 0 END)) AS aaa
FROM WS64
GROUP BY KQ, ZYMC
ORDER BY 考场

有分我再追加 就剩11分了
aaa就是想要合格人数/应考人数 但是结果都是0

Answer 1

整数运算最后结果也会只取整数部分
在计算时将其中一个数字*1.0转换成小数就可以得到含有小数的结果了

追问

如何解决呢？

追答

sum()*1.0/sum()