对表中重复记录的处理

qinsz 11/10/2023 MySQLsql

# 1. 将所有重复项中id号最小id选出

SELECT MIN(id) 
FROM major_enrollment 
GROUP BY year, school_id, major_id, edu_len 
HAVING COUNT(*) > 1

1
2
3
4

通过GROUP BY按照重复项的约束条件将数据分组
然后通过HAVING COUNT(*) > 1 筛选出每组中数量大于1的数据，
可理解为是一种组内条件筛选
最后使用SELECT选择出每组中id值最小的id

# 2. 为查询的数据集添加序号

SELECT
  id,
  YEAR,
  school_id,
  major_id,
  edu_len,
  ROW_NUMBER() OVER
  (
    PARTITION BY YEAR,school_id,major_id,edu_len
    ORDER BY id
  ) AS RowNum 
FROM major_enrollment

1
2
3
4
5
6
7
8
9
10
11
12

使用ROW_NUMBER(): 窗口函数为查询集合分配一个序号
OVER (PARTITION BY year, school_id, major_id, edu_len ORDER BY id)：
为ROW_NUMBER()窗口函数指定排序规则。
- PARTITION BY: 表示窗口函数按指定的字段进行分组。
- ORDER BY id: 表示以id字段进行组内排序，默认使用升序方式。

# 3. 利用ROW_NUMBER()实现重复项的删除

WITH number_table AS (
  SELECT
    id,
    YEAR,
    school_id,
    major_id,
    edu_len,
    ROW_NUMBER() OVER 
    (
      PARTITION BY YEAR, school_id, major_id, edu_len
      ORDER BY id 
    ) AS RowNum 
  FROM major_enrollment 
)

DELETE FROM major_enrollment
WHERE id IN (
  SELECT id
  FROM number_table
  WHERE RowNum > 1
)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

使用ROW_NUMBER窗口函数形成带组内序号的中间表。
使用SELECT语句从中间表中筛选出RowNum>1所在行的id。
使用DELETE批量删除。