Select Query with Conditional用于记录组

Question

SELECT DISTINCT
    t2.cmdb_id,
    t1.src_sys_id,
    t3.appl_nm,
    t1.st_mgmnt_cd Status,
    CASE 
       WHEN t1.st_mgmnt_cd != "PURGE" 
          THEN 'In-Progress' 
       ELSE 'In-Compliance' 
    END as Status,
    t1.podium_delivery_date
FROM
    table1 t1 
JOIN 
    table2 t2 
JOIN 
    table3 t3 
WHERE
    t1.src_sys_id = t2.ingstn_nm 
    AND t2.cmdb_id = t3.cmdb_id

上面的查询返回状态列重复的许多行。这是因为在原始表上，除了purge列之外，我还有许多st_mgmnt_cd列的状态。但我需要检查cmdb_id下的所有记录。因此，对于cmdb_id，我只需要在根据check显示的结果和状态中有一行。

如何重写查询以跨cmdb_id的记录执行检查

cmdb_id schema_name        appl_nm  status  assessment_date
88      PAD                PADCOD   HOT     20180601
88      PAD                PADCOD   WARM    20180601
218     EED                EEDCOD   HOT     20180617
218     EED                EEDCOD   WARM    20180618
218     EED                EEDCOD   COLD    20180620
3106    ABC                ABCOD    HOT     20180601
3106    ABC                ABCOD    WARM    20180604
3106    ABC                ABCOD    EXPIRED 20180620
3106    ABC                ABCOD    PURGE   20180622

预期结果==================

 88   PAD   PADCOD  In-Progress     20180601
 218  EED   EEDCOD  In-Progress     20180620
 3106 ABC   ABCOD   In-Compliance   20180620

Answer 1

将您的查询封装在SELECT an change to max(st_mgmntcd) in internal query中。

即SELECT COL1，data FROM( SELECT COL1，MAX(REPETINGCOL) as data group by COL1) x

Answer 2

未来:使用一些可实时测试的东西

像DB-Fiddle一样

我省略了与其他表的连接，并添加了一个字段id来连接表temp，但是您应该理解这个概念。:)

假设您想要每个cmdb_id的最新条目。为此，我使用了新的字段id，但您也可以以相同的方式使用字段podium_delivery_date。

# assume joins have been made
SELECT
    t1.cmdb_id,
    t1.appl_nm,
    t1.st_mgmnt_cd Status,
    CASE 
       WHEN t1.st_mgmnt_cd != "PURGE" 
          THEN 'In-Progress' 
       ELSE 'In-Compliance' 
    END as Status,
    t1.podium_delivery_date
FROM
    table1 t1
    JOIN 
      (SELECT
          tx.cmdb_id,
          MAX(tx.id) as highest_id_of_group
      FROM
          table1 tx
      GROUP BY
          tx.cmdb_id) as temp
    ON temp.highest_id_of_group = t1.id

记住要放好索引！