1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
| -- 查询重复数据id
SELECT
GROUP_CONCAT( id SEPARATOR ',' )
FROM
`my_table`
GROUP BY
`user_id`,
`depart_id`,
`position_id`
HAVING
COUNT(*) > 1
AND `user_id` IS NOT NULL
AND `depart_id` IS NOT NULL
AND `position_id` IS NOT NULL;
-- 查询需要删除掉的重复数据
SELECT
t1.`id`,
t1.`user_id`,
t1.`depart_id`,
t1.`position_id`,
t1.`gmt_create`
FROM
my_table t1
INNER JOIN (
SELECT
user_id,
depart_id,
position_id,
MAX( gmt_create ) AS latest_gmt_create,
MAX( id ) AS latest_id
FROM
my_table
WHERE
user_id IS NOT NULL
AND depart_id IS NOT NULL
AND position_id IS NOT NULL
GROUP BY
user_id,
depart_id,
position_id
HAVING
COUNT(*) > 1
) t2 ON t1.user_id = t2.user_id
AND t1.depart_id = t2.depart_id
AND t1.position_id = t2.position_id
AND ( t1.gmt_create < t2.latest_gmt_create OR t1.id < t2.latest_id );
-- 改为删除sql，执行删除
DELETE
t1
FROM
my_table t1
INNER JOIN (
SELECT
user_id,
depart_id,
position_id,
MAX( gmt_create ) AS latest_gmt_create,
MAX( id ) AS latest_id
FROM
my_table
WHERE
user_id IS NOT NULL
AND depart_id IS NOT NULL
AND position_id IS NOT NULL
GROUP BY
user_id,
depart_id,
position_id
HAVING
COUNT(*) > 1
) t2 ON t1.user_id = t2.user_id
AND t1.depart_id = t2.depart_id
AND t1.position_id = t2.position_id
AND ( t1.gmt_create < t2.latest_gmt_create OR t1.id < t2.latest_id );
|