视图中的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