时间戳

首页 > 解决方案 > 相互比较行并根据条件仅保留一行

sql - 相互比较行并根据条件仅保留一行

问题描述

我有如下要求：

如果存在超过 1 条评论（姓名、姓氏、门、金额的组）并且其中一条包含，NULL则仅保留带有NULL评论的记录并丢弃其他评论。

如果NULL不是其中之一，并且注释包括NOT AVAILABLEand REQUIRES。保留NOT AVAILABLE丢弃REQUIRES。

Name    Lastname   Comment                    Amount    Door
------------------------------------------------------------
John    R.         NULL                          250    1
John    R.         NULL                          250    1
John    R.         New design is available       250    1
John    R.         Not available                 250    2
John    R.         Requires additional comment   250    2
John    R.         XYZ                           200    3
John    R.         Requires more information     200    4
John    R.         Requires more information     200    4
John    R.         Requires more information     200    4
John    R.         ABC                           200    4

结果应如下所示：

Name    Lastname   Comment                    Amount    Door
-------------------------------------------------------------
John    R.         NULL                          250    1
John    R.         Not available                 250    2
John    R.         XYZ                           200    3
John    R.         Requires more information     200    4
John    R.         Requires more information     200    4
John    R.         Requires more information     200    4
John    R.         ABC                           200    4

它应该只检查那些评论超过 1 的行。问题陈述是这样的，它应该检查NULL评论和不可用的评论并丢弃其他的，但如果这两个不存在，那么数据应该保持原样。

我正在尝试编写 CTE 以获得结果，但不确定如何比较评论部分。像下面的东西

WITH RNs AS
(
    SELECT 
        name,
        lastname,
        door,
        package,
        DENSE_RANK() OVER (PARTITION BY name,lastname, comment, amount, door 
                           ORDER BY name, lastname, amount, door ASC) AS RN
    FROM 
        test
)

标签： sqlsql-servertsql

解决方案

我敢肯定，有人可能会想出一些更优雅的东西，但是这会使用您的示例数据产生所需的输出。

这会根据您对分组分类的要求进行分区，以便按组按顺序对行进行排序，并在每个组内另外按第二个排序标准对not available/requires评论进行排名。

然后它会为每组创建一个总和，以计算每组的出现次数null/not available。

然后它从每个组中选择第一行，或者在没有多次出现 null/not available 的情况下选择第一行。

with cte as (
    select *, Row_Number() over (
            partition by name, lastname, amount, door
            order by case when comment like 'not available%' then 1 else case when comment like 'requires%' then 2 else 0 end end
        ) rn,
     Sum(case when comment is null or comment like 'not available%' then 1 else 0 end) over (partition by name, lastname, amount, door) gp
    from test
    )
select [Name], Lastname, Comment, Amount, Door
from cte
where rn=1 or gp=0
order by door, comment desc

推荐阅读