如何在SQL Server存储过程中按多个级别的列进行分组

时间：2021-09-29 22:33:46

I have this

我有这个

progress    Goal    Objective   actions
--------------------------------------
1001237    11723    NULL        NULL
1001237    11723    15094       NULL
1001237    11723    15094       24850
1001237    11723    15095       NULL
1001237    11723    15095       24853
1001237    11724    NULL        NULL
1001237    11724    15097       NULL
1001237    11724    15097       24857
1001237    11725    NULL        NULL
1001237    11726    NULL        NULL
1001237    11726    15098       NULL
1001237    11727    NULL        NULL
1001237    11727    15099       NULL

Data hierarchy is like this

数据层次结构是这样的

  • Progress 1:M Goal (Goal cannot be NULL)
  • 进度1：M目标（目标不能为NULL）
  • Goal 1:M Objective (Objective can be NULL)
  • 目标1：M目标（目标可以为NULL）
  • Objective 1:M Action (Action can be NULL)
  • 目标1：M动作（动作可以为NULL）

I would like to know how to write a query that will group goals, objectives and actions as in the below result set.

我想知道如何编写一个查询，将目标，目标和操作分组，如下面的结果集。

progress    Goal    Objective   actions
---------------------------------------
1001237     11723   15094       24850
1001237     11723   15095       24853
1001237     11724   15097       24857
1001237     11725   NULL        NULL
1001237     11726   15098       NULL
1001237     11727   15099       NULL

Important condition to note is to keep NULL rows if there is only NULL values under a particular category otherwise ignore NULL and keep only the meaningful values.

需要注意的重要条件是，如果特定类别下只有NULL值，则保留NULL行，否则忽略NULL并仅保留有意义的值。

For ex: NULL was ignored in the objective column under Goal 11723 because it has other valid values but for goal 11725 it was retained.

例如：在目标11723下的目标列中忽略了NULL，因为它具有其他有效值，但是对于目标11725，它被保留。

Similarly NULL in actions column was removed for objective 15097 but not for 15098.

类似地，对于目标15097，删除了动作列中的NULL，但不删除15098。

Please advice.

请指教。

1 个解决方案

#1

0  

Try this, not tested

试试这个，没有经过测试

select Progress, Goal, Objective ,Action 
from( 
    select Progress, Goal, Objective , Action
      , max(Action) over(partition by Progress, Goal, Objective) ma
      , max(Objective) over(partition by Progress, Goal) mo
      , max(Goal) over(partition by Progress) mg
     from MyTable) t 
where Action is not null 
        or ma is null and 
        (Objective is not null 
            or mo is null and 
            (Goal is not null 
              or mg is null)
        )

#1

0  

Try this, not tested

试试这个，没有经过测试

select Progress, Goal, Objective ,Action 
from( 
    select Progress, Goal, Objective , Action
      , max(Action) over(partition by Progress, Goal, Objective) ma
      , max(Objective) over(partition by Progress, Goal) mo
      , max(Goal) over(partition by Progress) mg
     from MyTable) t 
where Action is not null 
        or ma is null and 
        (Objective is not null 
            or mo is null and 
            (Goal is not null 
              or mg is null)
        )
标签：

相关文章