SQL 从 GROUP BY 结果中删除重复项

Question

我有一个具有以下结构的表

sys_id(identity) | id | group_id | fld_id | val
-----------------------------------------------

我有一个查询

SELECT id,group_id,fld_id,val,COUNT(*)
FROM [DB_ALERT].[dbo].[DATATABLE]
GROUP BY id,group_id,fld_id,val
HAVING COUNT(*)>1

结果集是这样的

   ID  | group_id | fld_id | val| count(*)
__________________________________________
1000001| 1        | 1      | 23 | 2
1000003| 1        | 1      | 24 | 5
1000008| 1        | 1      | 14 | 4

现在在结果集中，我只想为每条记录取前 1 个 sys_id 并删除具有相同 ID、Group、Fld 和 val 的其他记录(删除其重复项)。我知道如何使用游标来做到这一点，但是有没有办法在单个查询中进行这样的操作？

Answer 1

请尝试:

;with c as
(
    select *, row_number() over(partition by ID, Group, Fld, val order by ID, Group, Fld, val) as n
    from YouTable
)
delete from c
where n > 1