视图中的Case when语句错误－－在线等
状态值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
[解决办法]
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
[解决办法]
最后一个substring前多了个（
[解决办法]
除了上面几位说的语法错误外，你的substring语法也错了，取第一位应该用substring(status,1,1)
另外建议用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
[解决办法]
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
可不可以简化成这样啊？
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
