SQL:查找重复项,并为每个重复组分配该组的第一个副本的值

时间:2022-03-03 07:37:53

I have the results in the top table. I would like the results in the bottom table.

我把结果放在顶部表格中。我想在底部表中的结果。

SQL:查找重复项,并为每个重复组分配该组的第一个副本的值

Using an SQL query on the table above, I would like to find groups of duplicates (where the values in all columns except Id and Category are identical) and from that create a result that has for each entry the lowest Id from its group of duplicates and the (unmodified) Category from the original table.

在上面的表格中使用SQL查询,我想找到一组重复项(其中除了Id和Category之外的所有列中的值都相同),并从中创建一个结果,该结果对于每个条目,其重复项组中的最低Id和原始表中的(未修改的)类别。

1 个解决方案

#1


1  

Window function min can be used here:

窗函数min可以在这里使用:

select min(id) over (partition by first_name, last_name, company) id,
    category
from t;

#1


1  

Window function min can be used here:

窗函数min可以在这里使用:

select min(id) over (partition by first_name, last_name, company) id,
    category
from t;