状态值status是一个7位的字符串,比方说我想查询status分别为'1','2','3'开始的记录个数,用Case when语句做了一个视图,提示说无法使用“Case”语句
SELECT pdi1, COUNT(*) - SUM(CASE WHEN substring(status, 0, 1)
= '3' THEN 1 ELSE 0 END), COUNT(*) - SUM(CASE WHEN substring(status, 0, 1)
= '2' THEN 1 ELSE 0 END) - SUM(CASE WHEN substring(status, 0, 1)
= '3' THEN 1 ELSE 0 END), SUM(CASE WHEN substring(status, 0, 1)
= '2' THEN 1 ELSE 0 END) - SUM(CASE WHEN (substring(status, 7, 1)
= '1' THEN 1 ELSE 0 END)
FROM pdi
GROUP BY pdi1
同样,改用查询分析器 里面输入这段代码,提示“在关键字then附近有格式错误”各位高手帮我看看怎么错了,谢谢!
SELECT pdi1, COUNT(*) - SUM(CASE WHEN substring(status, 0, 1)
= '3' THEN 1 ELSE 0 END), COUNT(*) - SUM(CASE WHEN substring(status, 0, 1)
= '2' THEN 1 ELSE 0 END) - SUM(CASE WHEN substring(status, 0, 1)
= '3' THEN 1 ELSE 0 END), SUM(CASE WHEN substring(status, 0, 1)
= '2' THEN 1 ELSE 0 END) - SUM(CASE WHEN (substring(status, 7, 1)
= '1' THEN 1 ELSE 0 END)
FROM pdi
GROUP BY pdi1
同样,改用查询分析器 里面输入这段代码,提示“在关键字then附近有格式错误”各位高手帮我看看怎么错了,谢谢!
pdi1,
COUNT(*) - SUM(CASE WHEN substring(status, 0, 1)= '3' THEN 1 ELSE 0 END),
COUNT(*) - SUM(CASE WHEN substring(status, 0, 1)= '2' THEN 1 ELSE 0 END)
- SUM(CASE WHEN substring(status, 0, 1)= '3' THEN 1 ELSE 0 END),
SUM(CASE WHEN substring(status, 0, 1)= '2' THEN 1 ELSE 0 END)
- SUM(CASE WHEN substring(status, 7, 1)= '1' THEN 1 ELSE 0 END)
FROM pdi
GROUP BY pdi1
= '3' THEN 1 ELSE 0 END), COUNT(*) - SUM(CASE WHEN substring(status, 0, 1)
= '2' THEN 1 ELSE 0 END) - SUM(CASE WHEN substring(status, 0, 1)
= '3' THEN 1 ELSE 0 END), SUM(CASE WHEN substring(status, 0, 1)
= '2' THEN 1 ELSE 0 END) - SUM(CASE WHEN (substring(status, 7, 1) --substring之前多了一个"("
= '1' THEN 1 ELSE 0 END)
FROM pdi
GROUP BY pdi1
COUNT(*) -
SUM(CASE WHEN substring(status, 0, 1) = '3' THEN 1 ELSE 0 END), COUNT(*) - SUM(CASE WHEN substring(status, 0, 1) = '2' THEN 1 ELSE 0 END) - SUM(CASE WHEN substring(status, 0, 1) = '3' THEN 1 ELSE 0 END), SUM(CASE WHEN substring(status, 0, 1) = '2' THEN 1 ELSE 0 END) - SUM(CASE WHEN substring(status, 7, 1) = '1' THEN 1 ELSE 0 END)FROM pdi
GROUP BY pdi1
COUNT(*) - SUM(CASE WHEN substring(status, 0, 1) = '3' THEN 1 ELSE 0 END),
COUNT(*) - SUM(CASE WHEN substring(status, 0, 1) = '2' THEN 1 ELSE 0 END)
- SUM(CASE WHEN substring(status, 0, 1) = '3' THEN 1 ELSE 0 END),
SUM(CASE WHEN substring(status, 0, 1) = '2' THEN 1 ELSE 0 END)
- SUM(CASE WHEN substring(status, 7, 1) = '1' THEN 1 ELSE 0 END)
FROM pdi
GROUP BY pdi1
= '3' THEN 1 ELSE 0 END), COUNT(*) - SUM(CASE WHEN substring(status, 0, 1)
= '2' THEN 1 ELSE 0 END) - SUM(CASE WHEN substring(status, 0, 1)
= '3' THEN 1 ELSE 0 END), SUM(CASE WHEN substring(status, 0, 1)
= '2' THEN 1 ELSE 0 END) - SUM(CASE WHEN ( --这个(去掉
substring(status, 7, 1)
= '1' THEN 1 ELSE 0 END)
FROM pdi
GROUP BY pdi1
另外建议用like不用substring,写成下面形式比较好
SELECT
pdi1,
COUNT(*) - SUM(CASE WHEN status like '3%' THEN 1 ELSE 0 END),
COUNT(*) - SUM(CASE WHEN status like '[23]%' THEN 1 ELSE 0 END),
SUM(CASE WHEN status like '2%' THEN 1 ELSE 0 END)
- SUM(CASE WHEN status like '______1' THEN 1 ELSE 0 END)
FROM pdi
GROUP BY pdi1
COUNT(*) - SUM(CASE WHEN substring(status, 0, 1) = '3' THEN 1 ELSE 0 END),
COUNT(*) - SUM(CASE WHEN substring(status, 0, 1) = '2' THEN 1 ELSE 0 END)
- SUM(CASE WHEN substring(status, 0, 1) = '3' THEN 1 ELSE 0 END),
SUM(CASE WHEN substring(status, 0, 1) = '2' THEN 1 ELSE 0 END)
- SUM(CASE WHEN substring(status, 7, 1) = '1' THEN 1 ELSE 0 END)
FROM pdi
GROUP BY pdi1可不可以简化成这样啊?SELECT pdi1,
SUM(CASE WHEN status not like '3%' THEN 1 ELSE 0 END),
SUM(CASE WHEN status not like '[23]%' THEN 1 ELSE 0 END),
SUM(CASE WHEN status like '2%' THEN 1 when status like '1%' then -1 ELSE 0 END)
FROM pdi
GROUP BY pdi1
SELECT pdi1,
SUM(CASE WHEN status not like '3%' THEN 1 ELSE 0 END),
SUM(CASE WHEN status not like '[23]%' THEN 1 ELSE 0 END),
SUM(CASE WHEN status like '2%' THEN 1 when status like '1%' then -1 ELSE 0 END)
FROM pdi
GROUP BY pdi1