使用where子句删除重复项(Deleting duplicates with a where clause)

嘿所以我有一组结构数据：

id product_number product_type 1 1001 car 2 1002 house

但是数据有一些重复，其中：

id product_number product_type 1 1001 car 2 1001 house

我需要删除重复项，但只删除= house的值。

在我看来，查询应该是这样的：

DELETE * FROM table WHERE product_number is duplicate AND product_type = house

谢谢

Hey so I have one set of data with the structure:

id product_number product_type 1 1001 car 2 1002 house

But the data has some duplicates where:

id product_number product_type 1 1001 car 2 1001 house

I need to delete the duplicates but only the value which is = house.

In my mind the query should be like:

DELETE * FROM table WHERE product_number is duplicate AND product_type = house

Thanks

最满意答案

在MySQL中，您可以通过join ：

delete t from table t join (select product, count(*) as cnt from table group by product ) tt on tt.product = t.product where tt.cnt > 1 and t.product_type = 'house';

In MySQL, you can do what you want with a join:

delete t from table t join (select product, count(*) as cnt from table group by product ) tt on tt.product = t.product where tt.cnt > 1 and t.product_type = 'house';使用where子句删除重复项(Deleting duplicates with a where clause)

嘿所以我有一组结构数据：

id product_number product_type 1 1001 car 2 1002 house

但是数据有一些重复，其中：

id product_number product_type 1 1001 car 2 1001 house

我需要删除重复项，但只删除= house的值。

在我看来，查询应该是这样的：

DELETE * FROM table WHERE product_number is duplicate AND product_type = house

谢谢

Hey so I have one set of data with the structure:

id product_number product_type 1 1001 car 2 1002 house

But the data has some duplicates where:

id product_number product_type 1 1001 car 2 1001 house

I need to delete the duplicates but only the value which is = house.

In my mind the query should be like:

DELETE * FROM table WHERE product_number is duplicate AND product_type = house

Thanks

最满意答案

在MySQL中，您可以通过join ：

delete t from table t join (select product, count(*) as cnt from table group by product ) tt on tt.product = t.product where tt.cnt > 1 and t.product_type = 'house';

In MySQL, you can do what you want with a join:

delete t from table t join (select product, count(*) as cnt from table group by product ) tt on tt.product = t.product where tt.cnt > 1 and t.product_type = 'house';