SQL情况下的多重条件

Question

我有3张桌子要加入：

ALERT_CASE_HEADER
 - alert_key
ALERT_ITEM
- entity_key
-status_id
-entity_name='Active Alert'
STATUSES
-name
-id

两个警报表都使用alert_key和entity_key进行链接。只有当两个键对每个可用状态名称都是相同的值和entity_name='Active Alert'时，大小写的求和才会发生。和表状态中的id列链接到ALERT_ITEM.status_id。

我能做这样的事吗？我试着运行这个脚本，但是它看起来很慢。

SELECT 
  SUM(
    CASE
      WHEN s.name = 'Pending' 
      AND ach.alert_key = ai.entity_key 
      AND ai.entity_name = 'Active Alert' 
      THEN 1 
      ELSE 0 
    END
  ) AS PENDING, SUM(
    CASE
      WHEN s.name = 'new' 
      AND ach.alert_key = ai.entity_key 
      AND ai.entity_name = 'Active Alert' 
      THEN 1 
      ELSE 0 
    END
  ) AS NEW, SUM(
    CASE
      WHEN s.name = 'cancel' 
      AND ach.alert_key = ai.entity_key 
      AND ai.entity_name = 'Active Alert' 
      THEN 1 
      ELSE 0 
    END
  ) AS CANCEL 
FROM
  STATUSES s, ALERT_CASE_HEADER ach, ALERT_ITEM ai;

(从提问者11月14日‘17日9点53分的回答来看，这应该是编辑：)

我使用的脚本如下所示，只需关注2 join statements.The第二次联接就可以了，但第一次连接就不行了。

 SELECT CUS.ORGUNIT_CODE AS ORGANIZATION_UNITS,
  SUM(
    CASE
      WHEN S.name = 'Pending' THEN 1 ELSE 0 
    END
    ) AS PENDING_AUTOCLOSURE, 
  SUM(
    CASE
      WHEN S.name = 'New' THEN 1 ELSE 0 
    END
    ) AS NEW, 
  SUM(
    CASE
      WHEN S.name = 'Under Investigation' THEN 1 ELSE 0 
    END
  ) AS UNDER_INVESTIGATION,
  SUM(
    CASE
       WHEN S.Name = 'Escalated' THEN 1 ELSE 0
    END
  ) AS ESCALATED,
   SUM(
    CASE
       WHEN S.Name = 'Recommend True Positive' THEN 1 ELSE 0
    END
  ) AS RECOMMEND_TRUE_POSITIVE,
   SUM(
    CASE
       WHEN S.Name = 'Reopen Under Investigation' THEN 1 ELSE 0
    END
  ) AS REOPEN_UNDER_INVESTIGATION
FROM
  STATUSES S,CUSTOMERS CUS,ALERT_ITEM AI
  JOIN ALERT_ITEM AI ON S.ID = AI.STATUS_ID
  JOIN ALERT_CASE_HEADER ACH ON AI.ENTITY_KEY = ACH.ALERT_KEY
WHERE AI.ENTITY_NAME = 'Active Alert'
AND   AI.ORGUNIT_ID = CUS.ORGUNIT_ID
GROUP BY CUS.ORGUNIT_CODE;

Answer 1

从您的代码来看，我建议您以这种方式重构查询

在情况下使用表之间的联接而不是条件

  SELECT 
    SUM(
      CASE
        WHEN s.name = 'Pending' 
        THEN 1 
        ELSE 0 
      END
    ) AS PENDING
    , SUM(
      CASE
        WHEN s.name = 'new' 
        THEN 1 
        ELSE 0 
      END
    ) AS NEW
   , SUM(
      CASE
        WHEN s.name = 'cancel' 
        THEN 1 
        ELSE 0 
      END
    ) AS CANCEL 
  FROM STATUSES s
  INNER JOIN ALERT_ITEM ai ON s.id = ai.status_id 
      AND  ai.entity_name = 'Active Alert' 
  INNER JOIN  ALERT_CASE_HEADER ach ON ach.alert_key = ai.entity_key 

在新的OP代码之后更新

不要混合隐式和显式连接

   SELECT CUS.ORGUNIT_CODE AS ORGANIZATION_UNITS,
    SUM(
      CASE
        WHEN S.name = 'Pending' THEN 1 ELSE 0 
      END
      ) AS PENDING_AUTOCLOSURE, 
    SUM(
      CASE
        WHEN S.name = 'New' THEN 1 ELSE 0 
      END
      ) AS NEW, 
    SUM(
      CASE
        WHEN S.name = 'Under Investigation' THEN 1 ELSE 0 
      END
    ) AS UNDER_INVESTIGATION,
    SUM(
      CASE
         WHEN S.Name = 'Escalated' THEN 1 ELSE 0
      END
    ) AS ESCALATED,
     SUM(
      CASE
         WHEN S.Name = 'Recommend True Positive' THEN 1 ELSE 0
      END
    ) AS RECOMMEND_TRUE_POSITIVE,
     SUM(
      CASE
         WHEN S.Name = 'Reopen Under Investigation' THEN 1 ELSE 0
      END
    ) AS REOPEN_UNDER_INVESTIGATION
  FROM
    STATUSES S
    JOIN ALERT_ITEM AI ON S.ID = AI.STATUS_ID
    JOIN ALERT_CASE_HEADER ACH ON AI.ENTITY_KEY = ACH.ALERT_KEY
    INNER JOIN CUSTOMERS CUS ON  AI.ORGUNIT_ID = CUS.ORGUNIT_ID
  WHERE AI.ENTITY_NAME = 'Active Alert'
  GROUP BY CUS.ORGUNIT_CODE;

Answer 2

S.ID 00904无效标识符

ORA-00904: string:无效标识符 原因:输入的列名要么丢失要么无效。 操作:输入有效的列名。有效的列名必须以字母开头，小于或等于30个字符，并且仅由字母数字字符和特殊字符$、_和#组成。如果它包含其他字符，则必须用双引号括起来。它可能不是一个保留的词。

问题就在这几条线上。

FROM
  STATUSES S,CUSTOMERS CUS,ALERT_ITEM AI
  JOIN ALERT_ITEM AI ON S.ID = AI.STATUS_ID
  JOIN ALERT_CASE_HEADER ACH ON AI.ENTITY_KEY = ACH.ALERT_KEY
WHERE AI.ENTITY_NAME = 'Active Alert'

1. 第二行:为表状态定义别名S。
2. 第三行:别名S再次用于列S.ID ，该列是无效的，这意味着表STAUTUSES没有一个名为ID的列。
3. 第二行:您又添加了两个表，并再次使用逗号。这是坏的，非常坏的。永远不要将ANSI风格与“逗号语法”相结合，混合这些样式是做噩梦的秘诀。
4. 第二行和第三行:您重复了ALERT_ITEM AI

。

FROM
  STATUSES S

  ,CUSTOMERS CUS  /* change this to explicit join */

  JOIN ALERT_ITEM AI ON S.ID /* <<< wrong column name */   = AI.STATUS_ID

停止在表之间使用逗号.--如果您还记得这一点，您将有一个更轻松的SQL生活。

Answer 3

你可以用这个：

SELECT 
  SUM(
    CASE
      WHEN s.name = 'Pending' THEN 1 ELSE 0 
    END
    ) AS PENDING, 
  SUM(
    CASE
      WHEN s.name = 'new' THEN 1 ELSE 0 
    END
    ) AS NEW, 
  SUM(
    CASE
      WHEN s.name = 'cancel' THEN 1 ELSE 0 
    END
  ) AS CANCEL 
FROM
  STATUSES s  
  join ALERT_ITEM ai on s.id=ai.status_id
  join ALERT_CASE_HEADER ach on ai.entity_key=ach.alert_key
where ai.entity_name = 'Active Alert'