如何相互比较行并根据条件只保留一行 [英] How to compare rows with each other and keep only one row according to a condition

问题描述

我有如下要求:

• 如果存在超过 1 条评论(姓名、姓氏和门的组)并且其中一个包含 NULL，则仅保留带有 Null 评论的记录并丢弃其他评论.

• IF More than 1 comment exist (group of name, lastname and door) and one of them includes NULL then keep only the record with the Null comment and discard the others.

如果 Null 不是其中之一，并且注释包括 NOT AVAILABLE 和 REQUIRES.保持不可用 - 丢弃 REQUIRES

IF Null IS NOT one of them and the comment includes NOT AVAILABLE and REQUIRES. Keep Not available - discard REQUIRES

如果他们都只有 REQUIRES 选择金额或价值最低的记录.

IF all of them have only REQUIRES choose the record with the lowest amount or value.

    Name    Lastname   Comment                    Amount    Door
    John    R.         NULL                          250    1
    John    R.         NULL                          250    1
    John    R.         New design is available       250    1
    John    W.         Not available                 250    2
    John    W.         Requires additional comment   450    2
    John    S.         Requires further explanation  200    3
    John    S.         Requires more information     300    3

结果应如下所示:

    Name    Lastname   Comment                    Amount    Door
    John    R.         NULL                          250    1
    John    W.         Not available                 250    2
    John    S.         Requires further explanation  200    3

我正在尝试编写 CTE 以获得结果，但不确定如何比较评论部分，如下所示:

I am trying to write a CTE to get the result but not sure how to compare the comment section something like below:

WITH RNs AS(
    SELECT name,
           lastname,
           door,
           package,
           DENSE_RANK() OVER (PARTITION BY name ORDER BY door ASC) AS RN
    FROM test)
SELECT distinct name,
           lastname,
           door,package,
       CASE when package IS NULL  THEN 'PASS' 
            when package like 'Not available%' then 'PASS' 
            when package like 'requires%' then 'PASS' else 'fail' END AS to_keep_or_not
     
FROM RNs
GROUP BY RN,
         name,
           lastname,
           door,package;

解决这种问题陈述的最佳方法是什么?

What would be the best approach to solve this kind of problem statement?

推荐答案

你想要一个带有 case 表达式的 order by ...连同 ROW_NUMBER():

You want an order by with a case expression . . . along with ROW_NUMBER():

SELECT t.*
FROM (SELECT t.*,
             ROW_NUMBER() OVER (PARTITION BY name, lastname
                                ORDER BY (CASE WHEN comment IS NULL THEN 1
                                               WHEN comment LIKE '%NOT AVAILABLE%' THEN 2
                                               ELSE 3
                                          END),
                                         amount
                                ORDER BY door ASC
                               ) as seqnum
      FROM test t
     ) t
WHERE seqnum = 1;

这篇关于如何相互比较行并根据条件只保留一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！