从SQL Server数据集中删除特定记录的窗口函数

问题描述：

我有一个SQL Server查询，它返回一个数据集，显示一个人的ID，该人工作的实体，属于该实体的位置，分配给该案例的协调员和人的地位。从SQL Server数据集中删除特定记录的窗口函数

ID EntityName  LocationName  AssignedTo  StatusName 
    17  F&S   St. Lucie   A Hardon  Active 
    17  F&S   St. Lucie   A Hardon  Withdrawn 
    18  F&S   NH    A Hardon  Withdrawn 
    20  H&H   NCH    B Reedy  Active

我需要消除EntityName，LocationName和AssignedTo组合的唯一状态为Withdrawn的记录。所以在上面的数据集中，我想要用LocationName = NH删除ID = 18。

我使用窗口函数，这可能是在正确的道路上尝试，但我不知道如何着手：

Select id, entityname, locationname, assignedto, statusname 
    Into #test 
    From Table A 

    Select *, 
      row_number()over(partition by entityname, locationname, assignedto 
          order by case when statusname = 'Withdrawn' then 1 
            else 2 end) as rn 
    from #test

这给了我这样一个结果：

ID EntityName LocationName  AssignedTo  StatusName  RN 
    17  F&S   St. Lucie  A Hardon  Withdrawn  1 
    17  F&S   St. Lucie  A Hardon  Active   2 
    18  F&S   NH    A Hardon  Withdrawn  1 
    20  H&H   NCH    B Reedy  Active   1

但现在我被困在如何继续，或者如果我甚至以这种错误的方式去做。

'StatusName'可以是其他值吗？ –

是的，有7个状态，但我需要删除的唯一一个是撤回。 – jackstraw22

答

想想这个略有不同的方式。计数是而不是撤回的数字。如果这是0，则全部撤回。

适当的窗函数是有条件的聚集，而不是排名函数：

select t.* 
from (Select t.*, 
       sum(case when statusname <> 'Withdrawn' then 1 else 0 end) over 
        (partition by entityname, locationname, assignedto) as num_notwithdrawn 
     from #test t 
    ) t 
where num_notwithdrawn = 0;

当我尝试这样做时，它几乎消除了所有记录（约1,000），并且只保留了2，两者都是状态='撤回。“ – jackstraw22

@ jackstraw22。。'statusname'曾经'NULL'？ –

答

Select id, entityname, locationname, assignedto, statusname, 
    r row_number()over(partition by entityname, locationname, assignedto 
        order by CASE statusname WHEN 'Withdrawn' THEN 1 ELSE 2 END) as rn Into #test From Table A 
Select * from #test LEFT OUTER JOIN 
    (
     SELECT MAX(rn) MRNO,ID FROM #test GROUP BY ID 
) T ON T.ID = #TEST.ID WHERE rn = MRNO AND statusname ='Withdrawn'

这将eliinate与实体名称，LOCATIONNAME的组合单独撤销状态行，assignedto

这不会消除所有不是='撤回'的状态？ – jackstraw22

编辑查询 –

答

删除用INNER JOIN

DELETE T 
FROM Table T 
INNER JOIN 
(
    SELECT EntityName,LocationName,AssignedTo 
    FROM Table 
    WHERE StatusName='Withdrawn' 
    GROUP BY EntityName,LocationName,AssignedTo 
    HAVING COUNT(*) = 1 
)D ON D.EntityName=T.EntityName AND D.LocationName=T.LocationName AND D.AssignedTo=T.AssignedTo

我试了左连接 - 它很接近，但没有工作。有几个例子，一个人的ID可以属于多个位置的实体。如果其中一个实体只有一条记录，并且该记录='撤回'，那么该人的所有其他记录也将被删除。ID = 112，实体= F＆S，位置= NE佛罗里达州，状态=撤销。被删除，因为它应该是，但ID = 112的所有其他记录也被消除。 – jackstraw22

是的，那么你不能使用ID。我认为这是一个身份专栏，但是，它重复。您必须删除MAX（ID），然后通过外部查询中的字段加入这三个组，以使其发挥作用，但是，如果这三个字段没有编入索引，那么这可能会对大型数据集产生性能影响。 –

我更新了非标识符字段上的连接。 –

答

试试bel嗷嗷之一：

WITH CTE 
AS (
Select id, entityname, locationname, assignedto, statusname, 
     row_number()over(partition by entityname, locationname, assignedto 
         order by statusname) as rn 
    From Table A) 
DELETE from CTE where rn=1 and statusname = 'Withdrawn'

答

尝试使用EXISTS

Select * 
FROM #test t1 
WHERE EXISTS (SELECT * 
       FROM #test t2 
       WHERE t1.EntityName = t2.EntityName 
         and t1.LocationName = t2.LocationName 
         and t1.AssignedTo = t2.AssignedTo 
         and t2.StatusName <> 'Withdrawn')

我觉得这样做了！但我很困惑，为什么它会起作用。为什么这不会消除所有“撤回”状态？ – jackstraw22

Exists正在查看EntityName，LocationName，AssignedTo的组合是否存在于状态不是Withdrawn的表中。因此，对于第一条记录，它需要F＆S，St. Lucie和A Hardon在这个组合中搜索除了Withdrawn之外的任何状态。恰好如此，它发现了第一个记录（它自己），它是活动的，所以它保留了它。对于第二个记录，它也找到了自这些字段匹配以来的第一个记录并保持第二行..它就像使用IN（）过滤器，但允许您查找多个字段，而不是只有一个 – JamieD77

它花了我一段时间，但我想我理解它正在寻找第一个表中的所有记录它们在3个字段上匹配，但在第二个表中没有status ='Withdrawn'，这会从第一个表中返回所有记录，包括'Withdrawn'，因为实际上并没有从第一个表中过滤。唯一的记录表2中的d是'撤回'，它将从表2中过滤掉，因此不能加入到表1中。 – jackstraw22

答

为什么不能识别使用内部连接的潜力删除，然后添加一个简单的WHERE删除“撤回”条款项？

delete from #table a 
inner join (
      select entityname, locationname, assignedto, count(*) 
      from #table 
      group by entityname, locationname, assignedto 
      having count(*) = 1) b 
      on b.entityname = a.entityname 
      and b.locationname = a.locationname 
      and b.assignedto = a.assignedto 
where a.statusnname = 'Withdrawn'

内加入保持“势”缺失（E，L的任意组合和一个只用一个记录），并WHERE子句删除相应的记录（多个）后续的。

从SQL Server数据集中删除特定记录的窗口函数

相关推荐