mysql - SQL:需要按优先级获取 MySQL 中相关表的聚合状态

Question

我在 MySQL DB 5.5.x 中有一个包含列的父表 A

  Id(PK)  Name   Value
  1       ABC     0.1
  2       XYZ     0.2
  3       PQR     0.3

还有一个相关表 B，它引用父级 (FK) 的 Id状态只能有 3 个可能的值，即通过、警告、错误错误检查的优先级高于警告等

  CheckName  CheckStatus  Id
   L1         pass        1
   L2         pass        1
   L3         warn        1
   L4         error       1
   L1         pass        2
   L2         warn        2
   L3         pass        2

我想创建一个 SQL 语句，它可以从父表 A 获取聚合结果

如果 Id 1 有任何错误报告，我会将 Id 1 的 FinalState 标记为 ERROR，

如果没有发现错误，请检查 Id 1 是否报告了任何警告，并将 FinalState 标记为 WARN

最后标记为通过。

我无法执行简单的 JOIN 或在 select 子句中添加 case 语句当我开始在这里得到多行时。

我可以在不使用存储过程的情况下执行此操作吗？

基于最终选择查询中的示例数据的预期结果如下:-

Id  Name  FinalStatus
1   ABC   error
2   XYZ   warn

谢谢!

编辑:方法 1(我在这里尝试过):

select a.Id, 
   case when b.CheckStatus='error' then 'ERROR'
  case when b.CheckStatus='warn' then 'WARN'
  case when b.CheckStatus='pass' then 'PASS'             
 from a join b on
a.Id=b.Id

Answer 1

这是一个优先级查询。我只关注 b - 您可以使用简单的联接从 a 引入列。

这适用于 MySQL 8+:

select b.*
from (select b.*,
             row_number() over (partition by id
                                order by case checkstatus
                                             when 'error' then 1
                                             when 'warn' then 2
                                             when 'pass' then 3
                                             else 4
                                         end
                               ) as seqnum
      from b
     ) b
where seqnum = 1;

在早期版本中，我会选择条件聚合:

select b.id,
       max(finalstatus = 'error') as is_error,
       max(finalstatus = 'warn') as is_warn,
       max(finalstatus = 'pass') as is_pass
from b
group by b.id;

然后您可以获得最终状态:

select b.id,
       (case when max(checkstatus = 'error') > 0 then 'error'
             when max(checkstatus = 'warn') > 0 then 'warn'
             when max(checkstatus = 'pass') > 0 then 'pass'
        end) as finalstatus
from b
group by b.id;