TSQL 根据组行状态定义组状态

Question

嗨，我如何根据组行状态或我应该在谷歌上搜索的内容来定义组状态的任何想法？ 在下面的组中，状态是我所追求的。谢谢

Group  Row Status  Group Status
A      Good        Good
B      Good        Neutral
B      Neutral     Neutral
B      Bad         Neutral
C      Bad         Bad
D      Good        Error
D      Bad         Error
D      Error       Error

我上面的表格中有一个错误，好+中性+坏=坏，C应该是中性=中性，所以这将对@forpas和@Gordon的答案造成一些混淆 Linoff
我在下面添加了另一个表格以更好地展示案例/规则
任何带有错误 = 错误的
混合 任何混合 好中性 坏=坏
任何不同的状态 = 那个状态

Group   Row Status  Group Status
A       Good        Bad
A       Bad         Bad
A       Neutral     Bad
B       Good        Bad
B       Bad         Bad
C       Good        Bad
C       Neutral     Bad
D       Bad         Bad
D       Neutral     Bad
E       Good        Good
F       Bad         Bad
G       Neutral     Neutral
H       Error       Error
I       Good        Error
I       Bad         Error
I       Neutral     Error
I       Error       Error
J       Good        Error
J       Bad         Error
J       Error       Error
K       Good        Error
K       Neutral     Error
K       Error       Error
L       Bad         Error
L       Neutral     Error
L       Error       Error
M       Good        Error
M       Error       Error
N       Bad         Error
N       Error       Error
O       Neutral     Error
O       Error       Error

我能够使用@forpas方法获得我想要的结果。我认为这应该涵盖我的所有情况。

with cte as (
  select
    [Group],
    sum(case [Row Status] when 'Good' then 1 else 0 end) good,
    sum(case [Row Status] when 'Neutral' then 1 else 0 end) neutral,
    sum(case [Row Status] when 'Bad' then 1 else 0 end) bad,
    sum(case [Row Status] when 'Error' then 1 else 0 end) error
  from [Test].[dbo].[GroupStatus]
  group by [Group]
)
select 
  t.*,
  case 
    when c.error > 0 then 'Error'
    when c.good * c.neutral * c.bad > 0 then 'Bad'
    when c.good * c.neutral > 0 then 'Bad'
    when c.bad * c.neutral > 0 then 'Bad'
    when c.good * c.bad > 0 then 'Bad'
    when c.good > 0 then 'Good'
    when c.bad > 0 then 'Bad'
    when c.neutral > 0 then 'Neutral'
    else 'Error'
  end [Group Status] 
from [Test].[dbo].[GroupStatus] t inner join cte c
on c.[Group] = t.[Group]

Answer 1

在新的示例数据和说明之后进行编辑。
你可以用条件聚合来解决这个问题：

with cte as (
  select
    [Group],
    sum(case [Row Status] when 'Good' then 1 else 0 end) good,
    sum(case [Row Status] when 'Neutral' then 1 else 0 end) neutral,
    sum(case [Row Status] when 'Bad' then 1 else 0 end) bad,
    sum(case [Row Status] when 'Error' then 1 else 0 end) error
  from tablename
  group by [Group]
)
select 
  t.*,
  case 
    when c.error > 0 then 'Error'
    when (c.bad > 0) or (c.good * c.neutral > 0) then 'Bad'
    when c.neutral > 0 then 'Neutral'
    when c.good > 0 then 'Good'
    else 'Error'
  end [Group Status]
from tablename t inner join cte c
on c.[Group] = t.[Group]

请参阅演示。
结果：

> Group | Row Status | Group Status
> :---- | :--------- | :-----------
> A     | Good       | Bad         
> A     | Bad        | Bad         
> A     | Neutral    | Bad         
> B     | Good       | Bad         
> B     | Bad        | Bad         
> C     | Good       | Bad         
> C     | Neutral    | Bad         
> D     | Bad        | Bad         
> D     | Neutral    | Bad         
> E     | Good       | Good        
> F     | Bad        | Bad         
> G     | Neutral    | Neutral     
> H     | Error      | Error       
> I     | Good       | Error       
> I     | Bad        | Error       
> I     | Neutral    | Error       
> I     | Error      | Error       
> J     | Good       | Error       
> J     | Bad        | Error       
> J     | Error      | Error       
> K     | Good       | Error       
> K     | Neutral    | Error       
> K     | Error      | Error       
> L     | Bad        | Error       
> L     | Neutral    | Error       
> L     | Error      | Error       
> M     | Good       | Error       
> M     | Error      | Error       
> N     | Bad        | Error       
> N     | Error      | Error       
> O     | Neutral    | Error       
> O     | Error      | Error  

Answer 2

假设您有一个优先级，例如：

1. 错误
2. 中性
3. 坏
4. 好

您可以使用窗口函数：

select t.*,
       (case when sum(case when row_status = 'Error' then 1 else 0 end) over (partition by group) > 0
             then 'Error'
             when sum(case when row_status = 'Neutral' then 1 else 0 end) over (partition by group) > 0
             then 'Neutral'
             when sum(case when row_status = 'Bad' then 1 else 0 end) over (partition by group) > 0
             then 'Bad'
             else 'Good'
        end) as group_status             
from t;

编辑：

我认为您修改后的逻辑是：

select t.*,
       (case when min(row_status) over (partition by group) = max(row_status) over (partition by group)
             then min(row_status) over (partition by group)
             when sum(case when row_status = 'Error' then 1 else 0 end) over (partition by group) > 0
             then 'Error'
             else 'Bad'
        end) as group_status             
from t;