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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
| -- ============================================
| -- 修复重复车牌号问题
| -- ============================================
|
| -- 1. 查询重复的车牌号
| SELECT
| vehicle_no,
| COUNT(*) as count,
| GROUP_CONCAT(vehicle_id ORDER BY vehicle_id) as vehicle_ids,
| GROUP_CONCAT(platform_code ORDER BY vehicle_id) as platform_codes,
| GROUP_CONCAT(create_time ORDER BY vehicle_id) as create_times
| FROM tb_vehicle_info
| GROUP BY vehicle_no
| HAVING COUNT(*) > 1
| ORDER BY count DESC;
|
| -- 2. 查看具体重复记录的详情
| SELECT
| vehicle_id,
| vehicle_no,
| platform_code,
| device_id,
| car_id,
| status,
| create_time,
| update_time
| FROM tb_vehicle_info
| WHERE vehicle_no IN (
| SELECT vehicle_no
| FROM tb_vehicle_info
| GROUP BY vehicle_no
| HAVING COUNT(*) > 1
| )
| ORDER BY vehicle_no, vehicle_id;
|
| -- 3. 备份重复数据（在删除前）
| CREATE TABLE IF NOT EXISTS tb_vehicle_info_duplicate_backup AS
| SELECT * FROM tb_vehicle_info
| WHERE vehicle_no IN (
| SELECT vehicle_no
| FROM tb_vehicle_info
| GROUP BY vehicle_no
| HAVING COUNT(*) > 1
| );
|
| -- 4. 删除重复记录（保留最早创建的记录）
| -- 注意：执行前请先确认备份完成！
| -- 取消下面的注释来执行删除操作：
|
| /*
| DELETE v1 FROM tb_vehicle_info v1
| INNER JOIN tb_vehicle_info v2
| WHERE v1.vehicle_no = v2.vehicle_no
| AND v1.vehicle_id > v2.vehicle_id;
| */
|
| -- 5. 验证删除后的结果
| SELECT
| vehicle_no,
| COUNT(*) as count
| FROM tb_vehicle_info
| GROUP BY vehicle_no
| HAVING COUNT(*) > 1;
|
| -- 6. 添加唯一索引（防止未来出现重复）
| -- 注意：只有在确认没有重复数据后才能执行！
| -- 取消下面的注释来添加唯一索引：
|
| /*
| ALTER TABLE tb_vehicle_info
| ADD UNIQUE INDEX uk_vehicle_no (vehicle_no);
| */
|
| -- 7. 查询统计信息
| SELECT
| '总车辆数' as item,
| COUNT(*) as count
| FROM tb_vehicle_info
| UNION ALL
| SELECT
| '唯一车牌数' as item,
| COUNT(DISTINCT vehicle_no) as count
| FROM tb_vehicle_info
| UNION ALL
| SELECT
| 'CMS平台车辆' as item,
| COUNT(*) as count
| FROM tb_vehicle_info
| WHERE platform_code = 'CMS'
| UNION ALL
| SELECT
| 'GPS51平台车辆' as item,
| COUNT(*) as count
| FROM tb_vehicle_info
| WHERE platform_code = 'GPS51'
| UNION ALL
| SELECT
| 'LEGACY平台车辆' as item,
| COUNT(*) as count
| FROM tb_vehicle_info
| WHERE platform_code = 'LEGACY';
|
|
