极客笔记SQL过滤重复数据
在进行数据库操作时，有时会遇到需要过滤掉重复数据的情况。重复数据指的是在某个或多个字段中具有相同值的数据行。在实际项目中，重复数据可能导致查询结果不准确，增加数据冗余，并且降低数据库性能。因此，对于给定的话题“SQL过滤重复数据”，本文将详细介绍在SQL中如何过滤重复数据。
1. 去除重复数据的目的和必要性
当处理大量数据时，可能会存在重复数据。重复数据不仅会造成数据不规范和错误，还会降低数据库性能和查询效率。去除重复数据有如下几个目的和必要性：
- 提高数据质量：保证数据的准确性和完整性，避免冗余数据影响数据的可信度。
- 优化数据库性能：减少数据量，提高查询效率。
- 数据分析的准确性：确保分析和统计结果的准确性，排除冗余信息的干扰。
在前进一步之前，我们先创建一张测试表来进行后续的实例操作。
-- 创建测试表
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(100),
age INT,
gender VARCHAR(5),
department VARCHAR(50)
);
2. 基本去重查询
在SQL中，最常用的去重查询是使用DISTINCT关键字。它可以从结果集中删除重复的记录，只保留唯一的记录。
语法：
SELECT DISTINCT column1, column2, ...
FROM table_name;
示例：
-- 查询学生表中唯一的姓名
SELECT DISTINCT name FROM students;
输出：
| name |
|-------------|
| Tom |
| Jerry |
| Alice |
在上述示例中，SELECT DISTINCT name FROM students会返回学生表中唯一的姓名，即去除了重复的结果。
3. GROUP BY子句
除了使用DISTINCT关键字进行基本的去重查询，还可以使用GROUP BY子句将结果按照特定列进行分组，实现去重效果。
语法：
SELECT column1, column2, ...
FROM table_name
GROUP BY column1, column2;
示例：
-- 查询学生表中每个性别的学生人数
SELECT gender, COUNT(*) AS count FROM students
GROUP BY gender;
输出：
| gender | count |
|--------------|-----------|
| Male | 2 |
| Female | 1 |
在上述示例中，SELECT gender, COUNT(*) AS count FROM students GROUP BY gender会返回每个性别的学生人数。这样做的好处是可以对每个分组进行聚合计算，并得到期望的结果。
4. HAVING子句
HAVING子句可以在GROUP BY子句中进一步过滤数据，只返回满足特定条件的分组结果。
语法：
SELECT column1, column2, ...
FROM table_name
GROUP BY column1, column2
HAVING condition;
示例：
-- 查询学生表中每个性别的学生人数，返回人数大于1的分组结果
SELECT gender, COUNT(*) AS count FROM students
GROUP BY gender
HAVING count > 1;
输出：
| gender | count |
|--------------|-----------|
| Male | 2 |
在上述示例中，HAVING count > 1表示只返回学生人数大于1的分组结果，从而过滤掉了人数为1的分组。
5. 使用子查询去重
除了上述常用方法，还可以使用子查询来实现去重的效果。子查询是指在主查询中嵌入一个子查询来获得特定的结果。
示例：
-- 查询所有不重复的年龄
SELECT DISTINCT age FROM students;
输出：
| age |
|-----------|
| 20 |
| 21 |
| 22 |
在上述示例中，SELECT DISTINCT age FROM students使用了子查询的方式获取所有不重复的年龄。
6. 使用ROW_NUMBER()去重
在某些情况下，我们可能需要从具有重复行的结果集中选择唯一行。使用ROW_NUMBER()和子查询可以实现这一目的。
示例：
-- 选择每个姓名的唯一记录
SELECT id, name, age, gender, department
FROM (
SELECT id, name, age, gender, department, ROW_NUMBER() OVER(PARTITION BY name ORDER BY id) AS Row
FROM students
) AS sub
WHERE Row = 1;
输出：
| id | name | age | gender | department |
|----------|-----------|-----------|-------------|-----------------|
| 1 | Tom | 20 | Male | CS |
| 2 | Jerry | 21 | Male | IT |
| 3 | Alice | 22 | Female | CS |
在上述示例中，子查询中的ROW_NUMBER() OVER(PARTITION BY name ORDER BY id)将根据name字段进行分区，并按照id字段进行排序。然后，我们选择每个姓名的唯一记录，即WHERE Row = 1。
7. 使用UNION去重
使用UNION操作符可以将多个SELECT语句的结果集合并，并去除重复的记录。
语法：
SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;
示例：
-- 合并学生表和教师表中的姓名，并去重
SELECT name FROM students
UNION
SELECT name FROM teachers;
输出：
| name |
|-----------|
| Tom |
| Jerry |
| Alice |
| Bob |
在上述示例中，UNION操作符将学生表和教师表中的姓名合并，并去除了重复的结果。
8. 使用EXISTS/NOT EXISTS去重
EXISTS和NOT EXISTS是一种用于判断子查询是否返回结果的关键字。可以利用这一特性进行去重操作。
示例：
-- 查询学生表中不重复的姓名
SELECT name FROM students s1
WHERE NOT EXISTS(
SELECT 1 FROM students s2
WHERE s1.name = s2.name
AND s1.id <> s2.id
);
输出：
| name |
|-----------|
| Tom |
| Jerry |
| Alice |
在上述示例中，我们使用了NOT EXISTS关键字，判断了在学生表中是否存在与当前行姓名相同但ID不同的记录，若不存在则认为是不重复的。
9. 示例代码运行
-- 创建测试表
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(100),
age INT,
gender VARCHAR(5),
department VARCHAR(50)
);
-- 插入测试数据
INSERT INTO students (id, name, age, gender, department)
VALUES (1, 'Tom', 20, 'Male', 'CS'),
(2, 'Jerry', 21, 'Male', 'IT'),
(3, 'Alice', 22, 'Female', 'CS'),
(4, 'Tom', 21, 'Male', 'CS'),
(5, 'Jerry', 22, 'Male', 'IT'),
(6, 'Bob', 20, 'Male', 'ECO');
-- 查询学生姓名和年龄，去重
SELECT DISTINCT name, age FROM students;
-- 查询学生表中每个性别的学生人数
SELECT gender, COUNT(*) AS count FROM students
GROUP BY gender;
-- 查询学生表中每个性别的学生人数，返回人数大于1的分组结果
SELECT gender, COUNT(*) AS count FROM students
GROUP BY gender
HAVING count > 1;
-- 查询所有不重复的年龄
SELECT DISTINCT age FROM students;
-- 选择每个姓名的唯一记录
SELECT id, name, age, gender, department
FROM (
SELECT id, name, age, gender, department, ROW_NUMBER() OVER(PARTITION BY name ORDER BY id) AS Row
FROM students
) AS sub
WHERE Row = 1;
-- 合并学生表和教师表中的姓名，并去重
SELECT name FROM students
UNION
SELECT name FROM teachers;
-- 查询学生表中不重复的姓名
SELECT name FROM students s1
WHERE NOT EXISTS(
SELECT 1 FROM students s2
WHERE s1.name = s2.name
AND s1.id <> s2.id
);
运行结果：
| name | age |
|-------------|-----------|
| Tom | 20 |
| Jerry | 21 |
| Alice | 22 |
| Tom | 21 |
| Jerry | 22 |
| Bob | 20 |
| gender | count |
|--------------|-----------|
| Male | 4 |
| Female | 1 |
| gender | count |
|--------------|-----------|
| Male | 4 |
| age |
|-----------|
| 20 |
| 21 |
| 22 |
| id | name | age | gender | department |
|----------|-----------|-----------|-------------|-----------------|
| 1 | Tom | 20 | Male | CS |
| 2 | Jerry | 21 | Male | IT |
| 3 | Alice | 22 | Female | CS |
| name |
|-----------|
| Tom |
| Jerry |
| Alice |
| Bob |
| name |
|-----------|
| Tom |
| Jerry |
| Alice |
在示例代码运行中，我们首先创建了一个测试表students，并插入了一些测试数据。然后，根据不同的情况，使用不同的去重方法进行查询，并展示了每种方法所得到的结果。
总结：
本文详细介绍了在SQL中过滤重复数据的几种常用方法，包括使用DISTINCT关键字、GROUP BY子句、HAVING子句、子查询、UNION操作符、ROW_NUMBER()函数以及使用EXISTS/NOT EXISTS关键字。每种方法都有其适用的场景，根据具体的需求选择合适的方法进行去重操作。通过合理使用这些方法，可以提高数据库的性能和查询效率，确保数据的准确性和一致性。