视图中的Case when语句错误－－在线等

[jj601]

状态值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附近有格式错误”

各位高手帮我看看怎么错了，谢谢！

[OracleRoob]

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

[Hinco]

除了上面几位说的语法错误外，你的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

[jj601]

谢谢各位，请问是用like比较好呢，还是用left(status,1)好？

[子陌红尘]

如果status字段上建立了索引，而且从左侧开始匹配，用like效率高。

[txlicenhe]

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

[txlicenhe]

应该可以直接这样写，懒得测试了。
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

[jj601]

谢谢各位，马上结贴

[子陌红尘]

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) --substring之前多了一个"("
= '1' THEN 1 ELSE 0 END)
FROM pdi
GROUP BY pdi1

[OracleRoob]

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

[OracleRoob]

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

[toddzst]

最后一个substring前多了个（