在分区(层次结构)中选择最坏的值

Within each modulo number I want to select the worst class value from class column but only from rows with 'y' in y/n column, then i want to populate this class in each row for each modulo. I have no idea how to implement hierarchy that is:

在每个模块中，我想从类列中选择最差的类值，但只从y/n列中的“y”行来选择，然后我想要在每一行中为每个模块填充这个类。我不知道如何实现层次结构:

N is better than O, O is better than P, P is better than W, W is better than S (S is the worst class)

N比O好，O比P好，P比W好，W比S好(S是最差的类)

Example:

例子:

modulo    y/n    class
1         y      N
1         n      P
1         n      W
1         y      P
2         n      W
2         n      N
3         y      P
3         y      W
3         n      O
2         y      W
4         n      P
4         y      S

and what I would like to achieve:

我想要达到的目标是:

modulo    y/n    class   worst_class
1         y      N       W
1         n      P       W
1         n      W       W
1         y      P       W
2         n      W
2         n      N
3         y      P       S
3         y      S       S
3         n      O       S
1         y      W       W
4         n      S       P
4         y      P       P

In modulo '1' there are only three values with 'y': N, P and W. The worst value is W, so populate W for every row in modulo '1'. In modulo '2' there are no rows with 'y' so NULL, in modulo '3' there are two values with 'y': P and S. The worst is S. In modulo '4' there is only P with 'y' so populate P in each row.

在modulo '1中，只有三个值:N, P和W，最坏的值是W，所以在模1中每一行都填充W。在模2中，没有一行是“y”所以是NULL，在“3”中有两个“y”的值:P和s，最坏的是s，在模4中只有P和y，所以每一行都有P。

1 个解决方案

#1

The following query gets what you want as a query:

以下查询得到您想要的查询:

select m.*, mm.WorstClass
from modulo m left outer join
     (select modulo,
             (case when SUM(case when class = 'S' then 1 else 0 end) > 0 then 'S'
                   when sum(case when class = 'W' then 1 else 0 end) > 0 then 'W'
                   when sum(case when class = 'P' then 1 else 0 end) > 0 then 'P'
                   when sum(case when class = 'O' then 1 else 0 end) > 0 then 'O'
                   when sum(case when class = 'N' then 1 else 0 end) > 0 then 'N'
              end) as WorstClass
      from modulo
      where YN = 'y'
      group by modulo
     ) mm
     on m.modulo = mm.modulo;

Because this is Oracle, the update requires using a correlated subquery:

因为这是Oracle，更新需要使用相关的子查询:

update modulo m
  set WorstClass = 
      (select (case when SUM(case when class = 'S' then 1 else 0 end) > 0 then 'S'
                    when sum(case when class = 'W' then 1 else 0 end) > 0 then 'W'
                    when sum(case when class = 'P' then 1 else 0 end) > 0 then 'P'
                    when sum(case when class = 'O' then 1 else 0 end) > 0 then 'O'
                    when sum(case when class = 'N' then 1 else 0 end) > 0 then 'N'
               end) as WorstClass
       from modulo mm
       where YN = 'y' and mm.module = m.modulo
      )

This assumes that you have a column for the worst class. If you don't have one, then you need to add one using alter table.

这假设您有一个列用于最坏的类。如果您没有，那么您需要添加一个使用alter table。

#1