1.首先保证有个自增的字段id
#添加自增的序号列
ALTER TABLE 表名 ADD id INT AUTO_INCREMENT PRIMARY KEY;
2.先确定判断重复的字段(这里是“子订单编号”),然后用子查询来删除重复值,保留一条
SELECT *
FROM test
WHERE 子订单编号 IN (
SELECT 子订单编号
FROM (
-- 查询出所有重复的user_name
SELECT 子订单编号
FROM test
GROUP BY 子订单编号
HAVING COUNT(子订单编号)>1
) t1
)
AND id NOT IN (
SELECT t2.min_id
FROM (
-- 查询出所有重复的记录并各自只取其中一条(MIN(id)或MAX(id)都可以)
SELECT MIN(id) AS min_id
FROM test
GROUP BY 子订单编号
HAVING COUNT(子订单编号)>1
) t2
)