Java面试——SQL 语句题

优质博文：IT-BLOG-CN

一、行转列问题

现有表格A，按照以下格式排列；

先需要将表格转化为：

方法一：使用静态SQL

select '姓名',
sum(case '收入类型' when '年奖金' then '收入金额' else 0 end) 年奖金,
sum(case '收入类型' when '月工资' then '收入金额' else 0 end) 月工资
from A
group by '姓名'
1
2
3
4
5

方法二：使用 pivot：MySQL不支持

select * from
(
    select 姓名,收入类型,收入金额 from A
) test
pivot(sum(收入金额) for 收入类型 in ('月工资','年终奖')) pvt

1
2
3
4
5
6

二、准备工作：

【1】表名和字段

–1.学生表 
Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 
–2.课程表 
Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号 
–3.教师表 
Teacher(t_id,t_name) –教师编号,教师姓名 
–4.成绩表 
Score(s_id,c_id,s_score) –学生编号,课程编号,分数
1
2
3
4
5
6
7
8

【2】测试数据

--建表
--学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
--课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
--教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
--成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
--插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
--课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

--教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
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

三、练习题

【1】查询"01"课程比"02"课程成绩高的学生的信息及课程分数：当对一张表中的一列数据比较时，应当将一张表拆分为两张表；

SELECT st.*,sc.`s_score` AS '语文' ,sc2.`s_score` AS '数学' 
FROM student st 
LEFT JOIN score sc ON st.s_id=sc.`s_id` AND sc.`c_id`='01'
LEFT JOIN score sc2 ON st.s_id=sc2.`s_id` AND sc2.`c_id`='02'  
WHERE sc.`s_score` > sc2.`s_score`;
1
2
3
4
5

【2】查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩：分组在 having 之前，有函数表达式时，条件判断需要使用 having，同时主要成绩需要截取为两位；

SELECT s.`s_id`,s.`s_name`,ROUND(AVG(sc.`s_score`),2) AS '平均成绩' FROM student s
LEFT JOIN score sc ON s.`s_id` = sc.`s_id`
GROUP BY sc.`s_id`
HAVING AVG(sc.`s_score`) >= 60;
1
2
3
4

【3】查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩；

SELECT s.`s_id`,s.`s_name`,COUNT(sc.`c_id`) AS '选课总数',SUM(CASE WHEN sc.`s_score` IS NULL THEN 0 ELSE sc.`s_score` END) AS '总成绩' FROM student s 
LEFT JOIN score sc ON s.`s_id` = sc.`s_id`
GROUP BY sc.`s_id`
1
2
3

【4】查询学过 “张三” 老师授课的同学的信息；

SELECT s.* FROM student s 
LEFT JOIN score sc ON s.`s_id` = sc.`s_id`
LEFT JOIN course c ON sc.`c_id` = c.`c_id`
LEFT JOIN teacher t ON t.`t_id` = c.`t_id`
WHERE t.`t_name` = "张三"
1
2
3
4
5

【5】查询没学过"张三"老师授课的同学的信息；

 SELECT st.* FROM student st WHERE st.s_id NOT IN(
  SELECT sc.s_id FROM score sc WHERE sc.c_id IN (SELECT c.c_id FROM course c LEFT JOIN teacher t ON t.t_id=c.t_id WHERE  t.t_name="张三")
  )
1
2
3

【6】查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

SELECT s.* FROM student s
INNER JOIN score sc ON s.`s_id` = sc.`s_id` 
INNER JOIN score sc1 ON s.`s_id` = sc1.`s_id`
WHERE sc.`c_id`='01' AND sc1.`c_id`='02'

--方式二
SELECT a.* 
FROM
    student a,
    score b,
    score c
WHERE
    a.s_id = b.s_id
    AND a.s_id = c.s_id
    AND b.c_id = '01'
    AND c.c_id = '02';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

【7】查询至少有一门课与学号为"01"的同学所学相同的同学的信息

SELECT DISTINCT s.* FROM student s 
LEFT JOIN score c ON s.`s_id` = c.`s_id`
WHERE c.`c_id` IN (
    SELECT sc.`c_id` FROM student s
    LEFT JOIN score sc ON s.`s_id` = sc.`s_id`
    WHERE s.`s_id`='01'
);
1
2
3
4
5
6
7

【8】查询和"01"号的同学学习的课程完全相同的其他同学的信息

SELECT DISTINCT s.* FROM student s 
LEFT JOIN score c ON s.`s_id` = c.`s_id`
GROUP BY s.`s_id`
HAVING COUNT(c.`c_id`) = (
    SELECT COUNT(sc.`c_id`) FROM student s
    LEFT JOIN score sc ON s.`s_id` = sc.`s_id`
    WHERE s.`s_id`='01'
);
1
2
3
4
5
6
7
8

【9】查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT s.`s_name` FROM student s
WHERE s.`s_id` NOT IN(
    SELECT sc.`s_id` FROM score sc
    LEFT JOIN course c ON sc.`c_id` = c.`c_id`
    LEFT JOIN teacher t ON t.`t_id` = c.`t_id`
    WHERE t.`t_name`="张三"
)
1
2
3
4
5
6
7

【10】查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT s.`s_id`,s.`s_name`,AVG(sc.`s_score`) FROM student s 
INNER JOIN score sc ON s.`s_id` = sc.`s_id`
WHERE s.`s_id` IN (
    SELECT sc.`s_id` FROM score sc 
    WHERE sc.`s_score`<60
    GROUP BY sc.`s_id`
    HAVING COUNT(1)>=2
)
GROUP BY s.`s_id`
1
2
3
4
5
6
7
8
9

【11】按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩：这里要注意 where 和 on 的区别：on 条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左（右）边表中的记录。(返回左（右）表全部记录)。此时可能会出现与右表不匹配的记录即为空的记录。即使on后边的条件不为真也会返回左（右）表中的记录。where 条件是在临时表生成好后，再对临时表进行过滤的条件。

SELECT s.`s_id`,s.`s_name`,sc.`s_score` AS "语文" ,sc1.`s_score` AS "数学",sc2.`s_score` AS "英语",AVG(sc3.`s_score`) "平均分" FROM student s 
LEFT JOIN score sc ON s.`s_id` = sc.`s_id` AND sc.`c_id` = "01"
LEFT JOIN score sc1 ON s.`s_id` = sc1.`s_id` AND sc1.`c_id` = "02"
LEFT JOIN score sc2 ON s.`s_id` = sc2.`s_id` AND sc2.`c_id` = "03"
LEFT JOIN score sc3 ON s.`s_id` = sc3.`s_id`  
GROUP BY s.`s_id`
ORDER BY AVG(sc3.`s_score`) DESC
1
2
3
4
5
6
7

【12】查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程 Name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率（及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90）

SELECT c.`c_id`,c.`c_name`,MAX(s.`s_score`) "最高分",MIN(s.`s_score`) "最低分",AVG(s.`s_score`) "平均分", 
((SELECT COUNT(1) FROM score sc WHERE sc.`c_id` = c.`c_id` AND sc.`s_score` >= 60)/(SELECT COUNT(1) FROM score sc WHERE sc.c_id = c.c_id)) "及格率",
((SELECT COUNT(1) FROM score sc WHERE sc.`c_id` = c.`c_id` AND 80 >= sc.`s_score` AND sc.`s_score` >= 70)/(SELECT COUNT(1) FROM score sc WHERE sc.c_id = c.c_id)) "中等率",
((SELECT COUNT(1) FROM score sc WHERE sc.`c_id` = c.`c_id` AND 90 >= sc.`s_score` AND sc.`s_score` >= 80)/(SELECT COUNT(1) FROM score sc WHERE sc.c_id = c.c_id)) "优良率",
((SELECT COUNT(1) FROM score sc WHERE sc.`c_id` = c.`c_id` AND sc.`s_score` >= 90)/(SELECT COUNT(1) FROM score sc WHERE sc.c_id = c.c_id)) "优秀率"
FROM course c
LEFT JOIN score s ON c.`c_id` = s.`c_id`
GROUP BY c.`c_id`; 
1
2
3
4
5
6
7
8

【13】查询所有课程的成绩第2名到第3名的学生信息及该课程成绩：Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；Union All：对两个结果集进行并集操作，包括重复行，不进行排序；注意 limit下标是从0开始的。

(SELECT s.*,c.`c_name`,sc.`s_score` "成绩" FROM student s
LEFT JOIN score sc ON s.`s_id` = sc.`s_id` AND sc.`c_id`="01"
LEFT JOIN course c ON sc.`c_id` = c.`c_id`
ORDER BY sc.`s_score` DESC
LIMIT 1,2)
UNION ALL
(SELECT s.*,c.`c_name`,sc.`s_score` "成绩" FROM student s
LEFT JOIN score sc ON s.`s_id` = sc.`s_id` AND sc.`c_id`="02"
LEFT JOIN course c ON sc.`c_id` = c.`c_id` 
ORDER BY sc.`s_score` DESC
LIMIT 1,2)
UNION ALL
(SELECT s.*,c.`c_name`,sc.`s_score` "成绩" FROM student s
LEFT JOIN score sc ON s.`s_id` = sc.`s_id` AND sc.`c_id`="03"
LEFT JOIN course c ON sc.`c_id` = c.`c_id` 
ORDER BY sc.`s_score` DESC
LIMIT 1,2)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

【14】查询学生平均成绩及其名次：重点是名次的获取，通过变量 @i 进行递增获取。

SET @i=0;
SELECT test.*,@i:=@i+1 "名次" FROM(
SELECT s.`s_name`,ROUND(AVG(sc.`s_score`),2) "平均成绩" FROM score sc
LEFT JOIN student s ON s.`s_id` = sc.`s_id`
GROUP BY sc.`s_id`
ORDER BY AVG(sc.`s_score`) DESC) test;
1
2
3
4
5
6

【15】查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩：思路就是先查询一条数据，然后与表中的数据比较相同的成绩，且科目号不相同的数据行，如果大于1则返回当前行即可。逐行比较；

SELECT st.s_id,st.s_name,sc.c_id,sc.s_score FROM student st 
LEFT JOIN score sc ON sc.s_id=st.s_id
LEFT JOIN course c ON c.c_id=sc.c_id
WHERE (
SELECT COUNT(1) FROM student st2 
LEFT JOIN score sc2 ON sc2.s_id=st2.s_id
LEFT JOIN course c2 ON c2.c_id=sc2.c_id
WHERE sc.s_score=sc2.s_score AND c.c_id!=c2.c_id 
)>=1
1
2
3
4
5
6
7
8
9

【16】 查询每门功成绩最好的前两名

SELECT a.* FROM (SELECT st.s_id,st.s_name,c.c_name,sc.s_score FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id
INNER JOIN course c ON c.c_id=sc.c_id AND c.c_id="01"
ORDER BY sc.s_score DESC LIMIT 0,2) a
UNION ALL
SELECT b.* FROM (SELECT st.s_id,st.s_name,c.c_name,sc.s_score FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id
INNER JOIN course c ON c.c_id=sc.c_id AND c.c_id="02"
ORDER BY sc.s_score DESC LIMIT 0,2) b
UNION ALL
SELECT c.* FROM (SELECT st.s_id,st.s_name,c.c_name,sc.s_score FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id
INNER JOIN course c ON c.c_id=sc.c_id AND c.c_id="03"
ORDER BY sc.s_score DESC LIMIT 0,2) c
1
2
3
4
5
6
7
8
9
10
11
12
13
14

方式二

SELECT a.s_id,a.c_id,a.s_score FROM score a
WHERE (SELECT COUNT(1) FROM score b WHERE b.c_id=a.c_id AND b.s_score>=a.s_score)<=2 ORDER BY a.c_id
1
2

【17】查询本周过生日的学生：此处可能有问题,week函数取的为当前年的第几周,2017-12-12是第50周而2018-12-12是第49周,可以取月份,day,星期几(%w)， 再判断本周是否会持续到下一个月进行判断,太麻烦。

SELECT st.* FROM student st 
WHERE WEEK(NOW())=WEEK(DATE_FORMAT(st.s_birth,'%Y%m%d'))
1
2

【18】查询下周过生日的学生

SELECT st.* FROM student st 
WHERE WEEK(NOW())+1=WEEK(DATE_FORMAT(st.s_birth,'%Y%m%d'))
1
2

【19】查询本月过生日的学生

SELECT st.* FROM student st 
WHERE MONTH(NOW())=MONTH(DATE_FORMAT(st.s_birth,'%Y%m%d'))
1
2

【20】查询下月过生日的学生： 注意，如果当前月为12月时，用month(now())+1为13而不是1，可用 timestampadd() 函数或 mod 取模

SELECT st.* FROM student st 
WHERE MONTH(TIMESTAMPADD(MONTH,1,NOW()))=MONTH(DATE_FORMAT(st.s_birth,'%Y%m%d'))
1
2

方法二：

SELECT st.* FROM student st WHERE (MONTH(NOW()) + 1) MOD 12 = MONTH(DATE_FORMAT(st.s_birth,'%Y%m%d'))
1