时间戳

首页 > 解决方案 > Select Query with Conditional Check for group of records

sql - Select Query with Conditional Check for group of records

问题描述

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

The above query returns many rows where the status column is repeated. This is because on the original table, I have many states other than purge for the column st_mgmnt_cd. But I need to do the check for all records under a cmdb_id. Hence for a cmdb_id, I need to have only one row in the results and status shown based on check.

How can I rewrite the query to do the check across the records of a 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

Results expected ==================

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

标签： sqlimpala

解决方案

将您的查询封装在 SELECT 中，并在内部查询中对 max(st_mgmntcd) 进行更改。

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

推荐阅读