时间戳

首页 > 解决方案 > 结合三张表的case语句

sql - 结合三张表的case语句

问题描述

SELECT 
    RS00200M.ACTION_NO,RS00200M.ACTION_CD,
    CAST(RS00200M.RESOLUTION_DT AS DATE) AS RESOLVED_DATE,
    CASE 
       WHEN (RS00200M.RESOLUTION_CD = 'CMPLT') 
          THEN (1) ELSE (0) 
    END AS COUNT_COMPLETE_SO,
    CASE 
       WHEN (RS00200M.ACTION_CD IN ('MTFRD', 'MTSRD')) 
          THEN (1) ELSE (0) 
    END AS COUNT_FINAL_INITIAL_SO,
    CASE 
       WHEN (RS00200M.ACTION_CD = 'RERTW') 
          THEN (1) ELSE (0) 
    END AS COUNT_ERT_CHANGE_SO,
    CASE 
       WHEN (RS00200M.ACTION_CD = 'MTEXE' AND UM00300M.SHORT IN ('058', '034', '100'))
          THEN (1) ELSE (0) 
    END AS COUNT_SHORT,
    CASE 
       WHEN (RS00200M.ACTION_CD = 'MTRRD') 
          THEN (1) ELSE (0) 
    END AS COUNT_REREAD_SO,
    CASE 
       WHEN (RS00200M.ACTION_CD = 'HIUSE') 
          THEN (1) ELSE (0) 
    END AS COUNT_HIUSE,
    CASE 
       WHEN (RS00200M.ACTION_CD = 'LKADJ') 
          THEN (1) ELSE (0) 
    END AS COUNT_LKADJ_SO
FROM
    RS00200M, UM00300M, RS00210M
LEFT JOIN 
    RS00210M ON RS00200M.ACTION_CD = RS00210M.ACTION_CD
LEFT JOIN 
    UM00300M ON UM00300M.SERIAL_NO = UM00210M.SERIAL_NO

我收到一个错误

消息 208，级别 16，状态 1，第 1 行
无效的对象名称“RS00210M”

有什么建议么？提前致谢——所有三个表都存在于数据库中

标签： sqlsql-server-2008case

解决方案

首先，您需要修复您的from条款。每个表应该只出现一次

FROM RS00200M
LEFT JOIN RS00210M on RS00200M.ACTION_CD=RS00210M.ACTION_CD
LEFT JOIN UM00300M on UM00300M.SERIAL_NO=UM00210M.SERIAL_NO

此外，您应该删除CASE表达式中这些多余的括号。通常，这是：

CASE WHEN (RS00200M.RESOLUTION_CD = 'CMPLT') THEN (1) ELSE (0) END AS COUNT_COMPLETE_SO

应该写成：

CASE WHEN RS00200M.RESOLUTION_CD = 'CMPLT' THEN 1 ELSE 0 END AS COUNT_COMPLETE_SO

这应该足以使您的查询运行没有错误。

旁注：您的查询结构往往表明您可能需要条件聚合。如果是这样，这是它的结构：

SELECT 
    RS00200M.ACTION_NO,
    RS00200M.ACTION_CD,
    CAST(RS00200M.RESOLUTION_DT AS DATE) AS RESOLVED_DATE,
    SUM(CASE WHEN RS00200M.RESOLUTION_CD = 'CMPLT' THEN 1 ELSE 0 END) AS COUNT_COMPLETE_SO,
    --- ... more SUM(CASE ...) expressions here ...
FROM RS00200M
LEFT JOIN RS00210M on RS00200M.ACTION_CD=RS00210M.ACTION_CD
LEFT JOIN UM00300M on UM00300M.SERIAL_NO=UM00210M.SERIAL_NO
GROUP BY
    RS00200M.ACTION_NO,
    RS00200M.ACTION_CD,
    CAST(RS00200M.RESOLUTION_DT AS DATE) AS RESOLVED_DATE

推荐阅读