SQL查询来聚合结果。

A single Site has multple PODs and Each POD has a status. The data is in a Table as shown below:

一个站点有多个豆荚，每个豆荚有一个状态。数据如下表所示:

Store   POD Status  
A001    S101    OK  
A001    S102    OK  
A001    S103    OK  
A002    S201    OK  
A002    S202    Critical  
A002    S203    OK  
A003    S301    OK  
A003    S302    OK  
A003    S303    Critical  
A004    S401    Warning  
A004    S402    OK  
A004    S403    OK  
A005    S501    OK  
A005    S502    OK  
A005    S503    OK  
A006    S601    OK  
A006    S602    OK  
A006    S603    Critical  
A007    S701    OK  
A007    S702    OK  
A007    S703    OK

Calculate the site status such that site should report Critical if any one of the PODs in a site has Critical Status, else if should report Warning if any one of the PODs in a site has warning,

计算站点的状态，如果站点中的任何一个豆荚具有临界状态，那么站点应该报告临界状态;如果站点中的任何一个豆荚具有临界状态，那么应该报告警报，

Expected Output  
A001 OK  
A002 Critical  
A003 Critical  
A004 Warning  
A005 OK  
A006 Critical  
A007 OK

What should be SQL query to get the expected output ?

什么是SQL查询才能获得预期的输出?

3 个解决方案

#1

You could use ELT and conditional aggregation:

您可以使用ELT和条件聚合:

SELECT Store, ELT(MAX(CASE WHEN Status='OK' THEN 1
                       WHEN status='Warning' THEN 2
                       WHEN status='Critical' THEN 3 END)
            , 'OK', 'Warning', 'Critical') AS output
FROM tab
GROUP BY Store;

DBFiddle Demo

DBFiddle演示

#2

SELECT
  store,
  ELT(
    MAX(CASE status WHEN 'OK'       THEN 1
                    WHEN 'Warning'  THEN 2
                    WHEN 'Critical' THEN 3
                                    ELSE 4 END),
    'OK',
    'Warning',
    'Critical',
    'Unknown'
  )
    AS final_status
FROM
  yourTable
GROUP BY
  store

#3

You can also use correlation approach:

你也可以使用相关方法:

select Store, (select t1.Status
               from table t1
               where t1.Store = t.Store
               order by (case when t1.Status = 'Critical' 
                              then 1  
                              when t1.Status = 'Warning' 
                              then 2
                              when t1.Status = 'OK' 
                              then 3
                         end)
                limit 1 
               ) as Status  
from table t
group by Store;

#1