SQLServer实战技巧：DISTINCT与子查询的高效应用

1. DISTINCT关键字的实战应用DISTINCT是SQLServer中最基础但极其重要的关键字之一。我在处理客户数据时发现很多开发者对这个关键字的理解仅停留在去重层面实际上它的应用场景要丰富得多。先来看一个典型的学生信息表案例-- 学生表结构示例 CREATE TABLE students ( sno VARCHAR(10) PRIMARY KEY, sname NVARCHAR(20), gender CHAR(1), nation NVARCHAR(20), class_id INT );假设我们要统计学校有哪些民族的学生新手常犯的错误是直接查询SELECT nation FROM students;这样会返回所有记录的民族字段包含大量重复值。正确的做法是SELECT DISTINCT nation FROM students;但这里有个坑需要注意DISTINCT对NULL值的处理。当nation字段存在NULL时查询结果会包含一个NULL记录。我在某次数据迁移项目中就因为这个特性导致下游系统报错后来通过添加过滤条件解决SELECT DISTINCT nation FROM students WHERE nation IS NOT NULL;多列去重是DISTINCT的进阶用法。比如要统计不同班级民族的组合情况SELECT DISTINCT class_id, nation FROM students ORDER BY class_id;这个查询会返回class_id和nation的唯一组合。有个性能优化技巧当DISTINCT配合ORDER BY使用时建议ORDER BY的列包含在DISTINCT的列中否则SQLServer需要额外排序操作。2. 子查询的核心原理与分类子查询就像SQL中的俄罗斯套娃我在优化一个电商系统查询时曾用多层子查询将原本需要5秒的查询优化到0.2秒。子查询主要分为两大类2.1 独立子查询不相关子查询这类子查询可以独立执行不依赖外部查询。比如查找教师人数最多的院系-- 独立子查询示例 SELECT deptno, dname FROM departments WHERE deptno ( SELECT TOP 1 deptno FROM teachers GROUP BY deptno ORDER BY COUNT(*) DESC );执行时数据库会先运行括号内的子查询得到教师最多的院系编号再用这个结果执行外部查询。这种子查询在查询计划中通常只执行一次。2.2 相关子查询关联子查询这类子查询引用了外部查询的列必须与外部查询配合执行。典型应用是计算每个学生的平均分SELECT sno, sname, ( SELECT AVG(grade) FROM score WHERE score.sno students.sno ) AS avg_grade FROM students;这里子查询中的score.sno引用了外部students表的sno字段。数据库会为students表的每一行都执行一次子查询性能开销较大。我在实际项目中遇到一个案例当学生表有1万条记录时这个查询需要执行1万次子查询后来改用JOIN优化SELECT s.sno, s.sname, AVG(sc.grade) AS avg_grade FROM students s LEFT JOIN score sc ON s.sno sc.sno GROUP BY s.sno, s.sname;3. 高级子查询技巧3.1 EXISTS与NOT EXISTS的妙用EXISTS是我最喜欢的子查询操作符之一它不关心子查询返回什么数据只关心是否有数据返回。这种特性让它比IN操作符更高效特别是在处理大数据量时。比如查找有挂科记录的学生SELECT sno, sname FROM students s WHERE EXISTS ( SELECT 1 FROM score sc WHERE sc.sno s.sno AND sc.grade 60 );这里有个性能优化点子查询中使用SELECT 1而不是SELECT *因为EXISTS只判断行是否存在不需要实际数据。NOT EXISTS的反向查询也很有用比如找出从未选课的学生SELECT sno, sname FROM students s WHERE NOT EXISTS ( SELECT 1 FROM score sc WHERE sc.sno s.sno );3.2 子查询与聚合函数的组合子查询经常与聚合函数配合使用。比如要找出成绩高于班级平均分的学生SELECT s.sno, s.sname, sc.grade FROM students s JOIN score sc ON s.sno sc.sno WHERE sc.grade ( SELECT AVG(grade) FROM score sc2 JOIN students s2 ON sc2.sno s2.sno WHERE s2.class_id s.class_id );这个查询中子查询为每个班级计算平均分外部查询则筛选出高于该平均分的记录。我在优化这类查询时发现为class_id建立索引可以显著提升性能。4. 性能优化实战经验4.1 DISTINCT的性能陷阱DISTINCT操作需要数据库对结果集进行排序和去重当处理大数据量时可能成为性能瓶颈。我曾遇到一个案例对500万条记录使用DISTINCT导致查询超时。优化方案是考虑是否真的需要所有列去重。比如-- 原始低效查询 SELECT DISTINCT * FROM large_table; -- 优化后查询 SELECT * FROM large_table WHERE id IN ( SELECT MIN(id) FROM large_table GROUP BY column1, column2 );4.2 子查询改写为JOIN很多子查询可以改写成JOIN形式通常性能更好。比如-- 子查询版本 SELECT name FROM products WHERE category_id IN ( SELECT id FROM categories WHERE type 电子 ); -- JOIN改写版本 SELECT p.name FROM products p JOIN categories c ON p.category_id c.id WHERE c.type 电子;但要注意NOT EXISTS在某些情况下比LEFT JOIN...IS NULL更高效因为前者找到第一个不匹配记录就会停止。4.3 临时表优化复杂子查询对于多层嵌套的复杂子查询可以考虑使用临时表-- 创建临时表存储中间结果 SELECT student_id, AVG(grade) AS avg_grade INTO #temp_avg FROM scores GROUP BY student_id; -- 使用临时表进行后续查询 SELECT s.name, t.avg_grade FROM students s JOIN #temp_avg t ON s.id t.student_id WHERE t.avg_grade 80; -- 删除临时表 DROP TABLE #temp_avg;这种方法虽然代码量增加但可读性和性能往往更好。我在处理一个三层嵌套子查询时用临时表将执行时间从8秒降到了1.3秒。