时间戳

首页 > 解决方案 > 无效标识符：sql中多列的总和

sql - 无效标识符：sql中多列的总和

问题描述

我正在尝试计算此查询中的多列

     SELECT
         SUM (CASE WHEN B.ID = 1 THEN 1 END)     AS OPD,
         SUM (CASE WHEN B.ID = 2 THEN 1 END)     AS IPD,
         SUM (CASE WHEN B.ID = 3 THEN 1 END)     AS DC,
         SUM (CASE WHEN B.ID = 4 THEN 1 END)     AS PROC,
         SUM (CASE WHEN B.ID = 5 THEN 1 END)     AS SUR,
(OPD + IPD + PROC) as Total
    FROM REF_TB_APP_TRANSACTIONS A,
         REF_VW_VISIT_TYPE      B
        
   WHERE     A.REQ_VISIT_TYPE = B.ID
         AND A.TO_EST_CODE = 20068;

但我收到此错误 PROC invalid identifier

标签： sqlsumcolumn-alias

解决方案

您不能直接SUMS在Total列中添加三个SELECT，因为您使用的是这些列的别名。你可以只Total用另一个SUM CASE.

SELECT
    SUM (CASE WHEN B.ID = 1 THEN 1 END)     AS OPD,
    SUM (CASE WHEN B.ID = 2 THEN 1 END)     AS IPD,
    SUM (CASE WHEN B.ID = 3 THEN 1 END)     AS DC,
    SUM (CASE WHEN B.ID = 4 THEN 1 END)     AS [PROC],
    SUM (CASE WHEN B.ID = 5 THEN 1 END)     AS SUR,
    SUM (CASE WHEN B.ID IN (1,2,4)THEN 1 END)     AS Total
FROM REF_TB_APP_TRANSACTIONS A,
        REF_VW_VISIT_TYPE      B
WHERE     A.REQ_VISIT_TYPE = B.ID
        AND A.TO_EST_CODE = 20068;

推荐阅读