SELECT
id
FROM
tablename a
WHERE
(a.stu_id, a.tea_id) IN (
SELECT
stu_id,
tea_id
FROM
tablename
GROUP BY
stu_id,
tea_id
HAVING
count(*) > 1
)
AND id NOT IN (
SELECT
min(id)
FROM
tablename
GROUP BY
stu_id,
tea_id
HAVING
count(*) > 1
)
通过上面语句能找出来数据库中stu_id,tea_id字段重复的数据,如果数据库中有3条重复的数据,这里将筛 选出来2条重复的数据,数据库中保留了一条最小id的数据。
表结构事例如下
CREATE TABLE `tablename` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`stu_id` bigint(20) NOT NULL DEFAULT '0',
`tea_id` bigint(20) NOT NULL DEFAULT '0',
`add_time` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '添加时间',
PRIMARY KEY (`id`),
KEY `tea_id` (`tea_id`),
KEY ` stu_id` (`stu_id`)
)