如何在使用GROUP子句的SQL查询中插入

Question

我正在使用ORACLE db，并试图为以下需求创建SQL查询。

以下是我的表快照：

 ID  STATUS
----------
 1     WORKING
 1     QUEUING
 1     SLEEPING
 2     FAILED
 2     SLEEPING
 2     SLEEPING
 3     QUEUING
 3     IDLE

预期产出：

 1  WORKING
 2  FAILED
 3  QUEUING

状态可以是工作状态、排队状态、失败状态、睡眠状态。

我想在SQL查询中插入以下条件，这些条件按首选项顺序列出：

• 如果组中的任何条目包含“工作”，我希望将最终结果设置为“工作”。
• 如果其中任何一项“失败”，则结果为“失败”。
• 如果任何一个条目是“排队”，那么结果就是“排队”；
• 默认“空闲”。

Answer 1

一个枢轴查询与一个子查询相结合应该会给你想要的东西：

SELECT t.ID,
    CASE WHEN t.Working > 0 THEN 'WORKING'
         WHEN t.Failed > 0  THEN 'FAILED'
         WHEN t.Queuing > 0 THEN 'QUEUING'
    ELSE 'IDLE'
    END AS STATUS
FROM
(
    SELECT ID,
        SUM(CASE WHEN STATUS = 'WORKING' THEN 1 ELSE 0 END) AS Working,
        SUM(CASE WHEN STATUS = 'FAILED'  THEN 1 ELSE 0 END) AS Failed,
        SUM(CASE WHEN STATUS = 'QUEUING' THEN 1 ELSE 0 END) AS Queuing
    FROM yourTable
    GROUP BY ID
) t

外部CASE表达式正确表示首选项的原因是因为Oracle总是执行short circuit evaluation。这意味着，如果查询遇到与WORKING匹配的ID，则将不执行其他两个检查(对于每个状态都适用类似的逻辑)。

Answer 2

如何在没有GROUP BY的情况下完成它

SELECT ID,
       CASE WHEN STATUS IN ( 'WORKING', 'FAILED', 'QUEUING' )
            THEN STATUS
            ELSE 'IDLE'
            END AS STATUS
FROM   (
  SELECT ID,
         STATUS,
         ROW_NUMBER() OVER (
           PARTITION BY ID
           ORDER BY CASE STATUS WHEN 'WORKING' THEN 1
                                WHEN 'FAILED'  THEN 2
                                WHEN 'QUEUING' THEN 3
                                ELSE                4 END
        ) AS rn
  FROM  table_name
)
WHERE  rn = 1;