我有以下称为存储的MySQL表

id ref item_no supplier
1  10    x1      usa
2  10    x1      usa
3  11    x1      china
4  12    x2      uk
5  12    x3      uk
6  13    x3      uk
7  13    x3      uk

现在我除了要输出的内容如下：

id ref item_no supplier
1  10    x1      usa
3  11    x1      china
4  12    x2      uk
5  12    x3      uk
6  13    x3      uk

如您所见,item_no x1和x3具有相同的引用和供应商来源,所以我想要的是按顺序删除重复记录以仅保留一个item_no！

我已经创建此PHP代码以仅选择结果：

$query1 = "SELECT 
                DISTINCT(item_no) AS field, 
                COUNT(item_no) AS fieldCount, 
                COUNT(ref) AS refcount 
            FROM 
                store 
            GROUP BY item_no HAVING fieldCount > 1";

$result1 = mysql_query($query1);

if(mysql_num_rows($result1)>0){
    while ($row1=mysql_fetch_assoc($result1)) {
        echo $row1['field']."<br /><br />";
    }
} else {
    //IGNORE
}

在创建DELETE查询之前,如何根据我的需要告诉查询SELECT重复记录正确.

多谢你们

解决方法:

您可以使用以下查询来生成所需的结果集：

SELECT t1.*
FROM store AS t1
JOIN (
   SELECT MIN(id) AS id, ref, item_no
   FROM store
   GROUP BY ref, item_no
) AS t2 ON t1.id > t2.id AND t1.ref = t2.ref AND t1.item_no = t2.item_no 

Demo here

要删除,您可以使用：

DELETE t1
FROM store AS t1
JOIN (
   SELECT MIN(id) AS id, ref, item_no
   FROM store
   GROUP BY ref, item_no
) AS t2 ON t1.id > t2.id AND t1.ref = t2.ref AND t1.item_no = t2.item_no