Java面试——SQL 语句题

优质博文：IT-BLOG-CN

一、行转列问题

现有表格A，按照以下格式排列；
姓名收入类型收入金额Tom年奖金5wTom月工资10kJack年奖金8wJack月工资12k
先需要将表格转化为：
姓名月工资年奖金Tom10k50kJack12k80k
方法一：使用静态SQL

select'姓名',sum(case'收入类型'when'年奖金'then'收入金额'else0end) 年奖金,sum(case'收入类型'when'月工资'then'收入金额'else0end) 月工资
from A
groupby'姓名'

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

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

二、准备工作：

【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) –学生编号,课程编号,分数

【2】测试数据

--建表--学生表CREATETABLE`Student`(`s_id`VARCHAR(20),`s_name`VARCHAR(20)NOTNULLDEFAULT'',`s_birth`VARCHAR(20)NOTNULLDEFAULT'',`s_sex`VARCHAR(10)NOTNULLDEFAULT'',PRIMARYKEY(`s_id`));--课程表CREATETABLE`Course`(`c_id`VARCHAR(20),`c_name`VARCHAR(20)NOTNULLDEFAULT'',`t_id`VARCHAR(20)NOTNULL,PRIMARYKEY(`c_id`));--教师表CREATETABLE`Teacher`(`t_id`VARCHAR(20),`t_name`VARCHAR(20)NOTNULLDEFAULT'',PRIMARYKEY(`t_id`));--成绩表CREATETABLE`Score`(`s_id`VARCHAR(20),`c_id`VARCHAR(20),`s_score`INT(3),PRIMARYKEY(`s_id`,`c_id`));--插入学生表测试数据insertinto Student values('01','赵雷','1990-01-01','男');insertinto Student values('02','钱电','1990-12-21','男');insertinto Student values('03','孙风','1990-05-20','男');insertinto Student values('04','李云','1990-08-06','男');insertinto Student values('05','周梅','1991-12-01','女');insertinto Student values('06','吴兰','1992-03-01','女');insertinto Student values('07','郑竹','1989-07-01','女');insertinto Student values('08','王菊','1990-01-20','女');--课程表测试数据insertinto Course values('01','语文','02');insertinto Course values('02','数学','01');insertinto Course values('03','英语','03');--教师表测试数据insertinto Teacher values('01','张三');insertinto Teacher values('02','李四');insertinto Teacher values('03','王五');--成绩表测试数据insertinto Score values('01','01',80);insertinto Score values('01','02',90);insertinto Score values('01','03',99);insertinto Score values('02','01',70);insertinto Score values('02','02',60);insertinto Score values('02','03',80);insertinto Score values('03','01',80);insertinto Score values('03','02',80);insertinto Score values('03','03',80);insertinto Score values('04','01',50);insertinto Score values('04','02',30);insertinto Score values('04','03',20);insertinto Score values('05','01',76);insertinto Score values('05','02',87);insertinto Score values('06','01',31);insertinto Score values('06','03',34);insertinto Score values('07','02',89);insertinto Score values('07','03',98);

三、练习题

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

SELECT st.*,sc.`s_score`AS'语文',sc2.`s_score`AS'数学'FROM student st 
LEFTJOIN score sc ON st.s_id=sc.`s_id`AND sc.`c_id`='01'LEFTJOIN score sc2 ON st.s_id=sc2.`s_id`AND sc2.`c_id`='02'WHERE sc.`s_score`> sc2.`s_score`;

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

SELECT s.`s_id`,s.`s_name`,ROUND(AVG(sc.`s_score`),2)AS'平均成绩'FROM student s
LEFTJOIN score sc ON s.`s_id`= sc.`s_id`GROUPBY sc.`s_id`HAVINGAVG(sc.`s_score`)>=60;

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

SELECT s.`s_id`,s.`s_name`,COUNT(sc.`c_id`)AS'选课总数',SUM(CASEWHEN sc.`s_score`ISNULLTHEN0ELSE sc.`s_score`END)AS'总成绩'FROM student s 
LEFTJOIN score sc ON s.`s_id`= sc.`s_id`GROUPBY sc.`s_id`

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

SELECT s.*FROM student s 
LEFTJOIN score sc ON s.`s_id`= sc.`s_id`LEFTJOIN course c ON sc.`c_id`= c.`c_id`LEFTJOIN teacher t ON t.`t_id`= c.`t_id`WHERE t.`t_name`="张三"

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

SELECT st.*FROM student st WHERE st.s_id NOTIN(SELECT sc.s_id FROM score sc WHERE sc.c_id IN(SELECT c.c_id FROM course c LEFTJOIN teacher t ON t.t_id=c.t_id WHERE  t.t_name="张三"))

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

SELECT s.*FROM student s
INNERJOIN score sc ON s.`s_id`= sc.`s_id`INNERJOIN 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';

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

SELECTDISTINCT s.*FROM student s 
LEFTJOIN score c ON s.`s_id`= c.`s_id`WHERE c.`c_id`IN(SELECT sc.`c_id`FROM student s
    LEFTJOIN score sc ON s.`s_id`= sc.`s_id`WHERE s.`s_id`='01');

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

SELECTDISTINCT s.*FROM student s 
LEFTJOIN score c ON s.`s_id`= c.`s_id`GROUPBY s.`s_id`HAVINGCOUNT(c.`c_id`)=(SELECTCOUNT(sc.`c_id`)FROM student s
    LEFTJOIN score sc ON s.`s_id`= sc.`s_id`WHERE s.`s_id`='01');

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

SELECT s.`s_name`FROM student s
WHERE s.`s_id`NOTIN(SELECT sc.`s_id`FROM score sc
    LEFTJOIN course c ON sc.`c_id`= c.`c_id`LEFTJOIN teacher t ON t.`t_id`= c.`t_id`WHERE t.`t_name`="张三")

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

SELECT s.`s_id`,s.`s_name`,AVG(sc.`s_score`)FROM student s 
INNERJOIN 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`<60GROUPBY sc.`s_id`HAVINGCOUNT(1)>=2)GROUPBY s.`s_id`

【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 
LEFTJOIN score sc ON s.`s_id`= sc.`s_id`AND sc.`c_id`="01"LEFTJOIN score sc1 ON s.`s_id`= sc1.`s_id`AND sc1.`c_id`="02"LEFTJOIN score sc2 ON s.`s_id`= sc2.`s_id`AND sc2.`c_id`="03"LEFTJOIN score sc3 ON s.`s_id`= sc3.`s_id`GROUPBY s.`s_id`ORDERBYAVG(sc3.`s_score`)DESC

【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`)"平均分",((SELECTCOUNT(1)FROM score sc WHERE sc.`c_id`= c.`c_id`AND sc.`s_score`>=60)/(SELECTCOUNT(1)FROM score sc WHERE sc.c_id = c.c_id))"及格率",((SELECTCOUNT(1)FROM score sc WHERE sc.`c_id`= c.`c_id`AND80>= sc.`s_score`AND sc.`s_score`>=70)/(SELECTCOUNT(1)FROM score sc WHERE sc.c_id = c.c_id))"中等率",((SELECTCOUNT(1)FROM score sc WHERE sc.`c_id`= c.`c_id`AND90>= sc.`s_score`AND sc.`s_score`>=80)/(SELECTCOUNT(1)FROM score sc WHERE sc.c_id = c.c_id))"优良率",((SELECTCOUNT(1)FROM score sc WHERE sc.`c_id`= c.`c_id`AND sc.`s_score`>=90)/(SELECTCOUNT(1)FROM score sc WHERE sc.c_id = c.c_id))"优秀率"FROM course c
LEFTJOIN score s ON c.`c_id`= s.`c_id`GROUPBY c.`c_id`;

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

(SELECT s.*,c.`c_name`,sc.`s_score`"成绩"FROM student s
LEFTJOIN score sc ON s.`s_id`= sc.`s_id`AND sc.`c_id`="01"LEFTJOIN course c ON sc.`c_id`= c.`c_id`ORDERBY sc.`s_score`DESCLIMIT1,2)UNIONALL(SELECT s.*,c.`c_name`,sc.`s_score`"成绩"FROM student s
LEFTJOIN score sc ON s.`s_id`= sc.`s_id`AND sc.`c_id`="02"LEFTJOIN course c ON sc.`c_id`= c.`c_id`ORDERBY sc.`s_score`DESCLIMIT1,2)UNIONALL(SELECT s.*,c.`c_name`,sc.`s_score`"成绩"FROM student s
LEFTJOIN score sc ON s.`s_id`= sc.`s_id`AND sc.`c_id`="03"LEFTJOIN course c ON sc.`c_id`= c.`c_id`ORDERBY sc.`s_score`DESCLIMIT1,2)

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

SET@i=0;SELECT test.*,@i:=@i+1"名次"FROM(SELECT s.`s_name`,ROUND(AVG(sc.`s_score`),2)"平均成绩"FROM score sc
LEFTJOIN student s ON s.`s_id`= sc.`s_id`GROUPBY sc.`s_id`ORDERBYAVG(sc.`s_score`)DESC) test;

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

SELECT st.s_id,st.s_name,sc.c_id,sc.s_score FROM student st 
LEFTJOIN score sc ON sc.s_id=st.s_id
LEFTJOIN course c ON c.c_id=sc.c_id
WHERE(SELECTCOUNT(1)FROM student st2 
LEFTJOIN score sc2 ON sc2.s_id=st2.s_id
LEFTJOIN course c2 ON c2.c_id=sc2.c_id
WHERE sc.s_score=sc2.s_score AND c.c_id!=c2.c_id 
)>=1

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

SELECT a.*FROM(SELECT st.s_id,st.s_name,c.c_name,sc.s_score FROM student st
LEFTJOIN score sc ON sc.s_id=st.s_id
INNERJOIN course c ON c.c_id=sc.c_id AND c.c_id="01"ORDERBY sc.s_score DESCLIMIT0,2) a
UNIONALLSELECT b.*FROM(SELECT st.s_id,st.s_name,c.c_name,sc.s_score FROM student st
LEFTJOIN score sc ON sc.s_id=st.s_id
INNERJOIN course c ON c.c_id=sc.c_id AND c.c_id="02"ORDERBY sc.s_score DESCLIMIT0,2) b
UNIONALLSELECT c.*FROM(SELECT st.s_id,st.s_name,c.c_name,sc.s_score FROM student st
LEFTJOIN score sc ON sc.s_id=st.s_id
INNERJOIN course c ON c.c_id=sc.c_id AND c.c_id="03"ORDERBY sc.s_score DESCLIMIT0,2) c

方式二

SELECT a.s_id,a.c_id,a.s_score FROM score a
WHERE(SELECTCOUNT(1)FROM score b WHERE b.c_id=a.c_id AND b.s_score>=a.s_score)<=2ORDERBY a.c_id

【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'))

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

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

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

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

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

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

方法二：

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