sql-subqueries-example

SQL Subqueries Explained with Tables

Let’s use a 学生 (Students) table for our examples:

学生表 (Students)

学号 (ID)	姓名 (Name)	年龄 (Age)	班级 (Class)	分数 (Score)
1	张三	18	A班	85
2	李四	19	B班	92
3	王五	20	A班	78
4	赵六	18	B班	96
5	钱七	21	A班	88

Now, let’s look at different types of subqueries:

1. 在WHERE子句中使用标量子查询

sql

SELECT 姓名, 分数
FROM 学生
WHERE 分数 > (SELECT AVG(分数) FROM 学生);

Result:

姓名	分数
李四	92
赵六	96
钱七	88

Explanation: This query returns students whose scores are above the average score (87.8).

2. 在FROM子句中使用表子查询

sql

SELECT 班级, 平均分数
FROM (
    SELECT 班级, AVG(分数) AS 平均分数
    FROM 学生
    GROUP BY 班级
) AS 班级平均分
WHERE 平均分数 > 85;

Result:

班级	平均分数
B班	94

Explanation: This query first calculates the average score for each class, then selects classes with an average score above 85.

3. 在SELECT子句中使用标量子查询

sql

SELECT 姓名, 
       分数, 
       (SELECT AVG(分数) FROM 学生) AS 平均分数,
       分数 - (SELECT AVG(分数) FROM 学生) AS 与平均分差距
FROM 学生;

Result:

姓名	分数	平均分数	与平均分差距
张三	85	87.8	-2.8
李四	92	87.8	4.2
王五	78	87.8	-9.8
赵六	96	87.8	8.2
钱七	88	87.8	0.2

Explanation: This query calculates each student’s score difference from the overall average score.

4. 使用IN和子查询

sql

SELECT 姓名, 班级
FROM 学生
WHERE 班级 IN (
    SELECT 班级
    FROM 学生
    GROUP BY 班级
    HAVING AVG(分数) > 85
);

Result:

姓名	班级
李四	B班
赵六	B班

Explanation: This query returns students who are in classes with an average score above 85.