emdzz

emdzz

关注
发信
关注(28)粉丝(399)

【DataBase】SQL45 Training 45题训练

视频地址：

https://www.bilibili.com/video/BV1pp4y1Q7Yv

创建案例库：

------------创建数据库---------------
create database data charset=utf8;

------------ 建表语句-----------------
# 学生表 Student：
create table Student(
SId varchar(10) ,
Sname varchar(10),
Sage datetime,
Ssex varchar(10));

# 教师表 Teacher
create table Teacher(
TId varchar(10),
Tname varchar(10));

# 科目表 Course
create table Course(
CId varchar(10),
Cname nvarchar(10),
TId varchar(10));

# 成绩表 SC
create table SC(
SId varchar(10),
CId varchar(10),
score decimal(18,1));

------------ 插入数据语句-----------------

# 学生表 Student：
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('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2017-12-30' , '女');
insert into Student values('12' , '赵六' , '2017-01-01' , '女');
insert into Student values('13' , '孙七' , '2018-01-01' , '女');

# 科目表 Course
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

# 教师表 Teacher
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

# 成绩表 SC
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

 1、课程01比课程02成绩高的学生信息和课程分数

-- 一、教程解法 【0.018s】
SELECT
    a.sid,
    a.sname,
    a.sage,
    a.ssex,
    b.score as '课程01',
    c.score as '课程02'
FROM
    student AS a
    INNER JOIN sc AS b ON a.sid = b.sid
    INNER JOIN sc AS c ON a.sid = c.sid AND b.cid = 01 AND c.cid = 02
WHERE
    b.score > c.score
-- 二、我的解法 【0.019s】 
SELECT
    D.sid,
    D.sname,
    D.sage,
    D.ssex,
    E.`课程01分数`,
    E.`课程02分数`
FROM
    student AS D,
    (
    SELECT
        A.sid,
        A.score AS '课程01分数',
        B.score AS '课程02分数'
    FROM
        sc AS A,
        sc AS B
    WHERE
        A.sid = B.sid
        AND A.cid = 01
        AND B.cid = 02
        AND A.score > B.score
    ) AS E
WHERE
    D.sid = E.sid

1.1、要求查询同时存在01和02课程的情况

用时【0.017s】

SELECT
    *
FROM
    sc as a,
    sc as b
WHERE
    1 = 1
    AND a.sid = b.sid
    AND a.cid = 01
    AND b.cid = 02

1.2、要求查询存在01, 可能存在02课程的情况（不存在显示为NULL）

-- SQL解法1 【0.017s】
SELECT
    *
FROM
    (SELECT * FROM sc WHERE cid = 01) AS a
    LEFT JOIN sc AS b
    ON a.sid = b.sid
    AND b.cid = 02

-- SQL解法2 【0.018s】
SELECT
    *
FROM
    sc AS a
    LEFT JOIN sc AS b
    ON a.sid = b.sid
    AND b.cid = 02
WHERE
    1 = 1
    AND a.cid = 01

1.3、要求查询不存在01,存在02课程的情况’

-- 解法1 【0.017s】会有多余记录
SELECT
    *
FROM
    (SELECT * FROM sc WHERE sid NOT IN (SELECT sid FROM sc WHERE cid = 01)) AS a
    INNER JOIN sc AS b
    ON a.sid = b.sid
    AND b.cid = 02
-- 解法2 【0.017s】
SELECT
    *
FROM
    sc as a
WHERE
    1 = 1
    AND sid not in (SELECT sid FROM sc where cid = 01)
    AND cid = 02

2、查询平均成绩大于60分的同学，学生编号和学生姓名和平均成绩

耗时【0.018s】

SELECT
    a.sid,
    a.sname,
    b.avg_score
FROM
    student as a,
    (SELECT sid,AVG(score) as avg_score FROM sc GROUP BY sid HAVING avg_score > 60) as b
WHERE
    1 = 1
    AND a.sid = b.sid

3、查询SC表存在成绩的学生信息

-- 我的解法 【0.016s】
SELECT DISTINCT b.* FROM sc as a, student as b WHERE a.sid = b.sid

-- 教程解法1 能够查出结果，但是SQL语法是有问题的，这是错误的示范
SELECT
    b.*
FROM
    sc AS a LEFT JOIN student AS b ON a.sid = b.sid
GROUP BY b.sid
-- 
> 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'devbase.b.Sname' 
which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by
--

-- 教程解法2 GROUP BY 允许对指定的列查询 【0.016s】
SELECT
    b.*
FROM
    (SELECT sid FROM sc GROUP BY sid) AS a
    LEFT JOIN student AS b
    ON a.sid = b.sid

4、查询所有学生的编号，姓名，选课数，所有课程的总成绩。没选课程则成绩为NULL

-- 首先在成绩表这里查询出选课数，和总成绩
SELECT sid,COUNT(cid) AS course_count, SUM(score) total_score FROM sc GROUP BY sid

-- 解法1 【0.018s】
SELECT
    a.sid,
    a.sname,
    b.course_count,
    b.total_score
FROM
    student AS a
    LEFT JOIN (SELECT sid,COUNT(cid) AS course_count, SUM(score) AS total_score FROM sc GROUP BY sid) AS b
    ON a.sid = b.sid;

-- 解法2 教程可查出，实际操作不支持此语法
SELECT
    a.sid,
    a.sname,
    COUNT(b.cid) AS '选课数',
    SUM(b.score) AS '总成绩'
FROM
    student AS a
    LEFT JOIN sc AS b
    ON a.sid = b.sid
GROUP BY a.sid
-- 
> 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'devbase.a.Sname' 
which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by
--

5、查询姓【李】的老师数量

-- 我的解法【0.017s】
SELECT count(tname) FROM teacher WHERE tname LIKE '李%'
-- 教程解法【0.016s】
SELECT count(1) FROM teacher WHERE tname LIKE '李%'

6、查询学过【张三】老师课程的学生信息

-- 我的解法，使用子查询作为逐个条件【0.017s】

-- 用教师表的名字找tid
SELECT tid FROM teacher WHERE tname = '张三'
-- 用tid 给课程表找cid
SELECT cid FROM course WHERE tid = 01
-- 用cid 给成绩表找sid
SELECT sid FROM sc WHERE cid = 02

-- 最后用成绩表查出的sid作为in条件给学生表查询
SELECT * FROM student WHERE sid IN (SELECT sid
FROM sc
WHERE cid = (
    SELECT cid
    FROM course
    WHERE tid = (
        SELECT tid
        FROM teacher
        WHERE tname = '张三'
    )
))

-- 教程解法 耗时【0.016s】
-- 1、老师和课程的关系
SELECT A.*, B.tname FROM course A INNER JOIN teacher B ON A.tid = B.tid
-- 2、教师和成绩的关系
SELECT
    *
FROM
    SC a
    INNER JOIN (SELECT b.*,c.tname FROM course b INNER JOIN teacher c ON b.tid = c.tid) d
    ON a.cid = d.cid
-- 3、得到学生和教师的关系
SELECT
    *
FROM
    student e
    INNER JOIN (
    SELECT
        a.*,
        d.cname,
        d.tid,
        d.tname
    FROM
        SC a
        INNER JOIN (SELECT b.*,c.tname FROM course b INNER JOIN teacher c ON b.tid = c.tid) d
        ON a.cid = d.cid
    ) f
    ON e.sid = f.sid
-- 4、追加老师等于张三的条件
SELECT
    *
FROM
    student e
    INNER JOIN (
    SELECT
        a.*,
        d.cname,
        d.tid,
        d.tname
    FROM
        SC a
        INNER JOIN (SELECT b.*,c.tname FROM course b INNER JOIN teacher c ON b.tid = c.tid) d
        ON a.cid = d.cid
    ) f
    ON e.sid = f.sid
WHERE f.tname = '张三'

7、查询没有全选课程的学生

-- 我的解法 耗时【0.018s】
-- 首先筛选全部课程
SELECT COUNT(1) FROM course
-- 筛选SID
SELECT
    sid,
    count(cid) course_count
FROM
    sc
GROUP BY sid
HAVING course_count <> (SELECT COUNT(1) FROM course)

-- 筛选学生
SELECT A.*
FROM student A,
(SELECT
    sid,
    count(cid) course_count
FROM
    sc
GROUP BY sid
HAVING course_count <> (SELECT COUNT(1) FROM course)) B
WHERE A.sid = B.sid

-- 教程解法1 不支持的语法
SELECT
    *
FROM
    student a
    LEFT JOIN sc b ON a.sid = b.sid
GROUP BY a.sid
HAVING COUNT(b.sid)    <> (SELECT COUNT(cid) FROM course)
-- 教程解法2 语法也不支持
SELECT
    a.*, b.*
FROM
    sc a
    INNER JOIN student b
    ON a.sid = b.sid
GROUP BY a.sid
HAVING count(1) < (SELECT COUNT(1) FROM course)

8、查询和学号01的同学，所学的课程至少同一个的学生

-- 我的解法 解法1
SELECT
    DISTINCT a.*
FROM
    student a INNER JOIN sc b ON a.sid = b.sid
WHERE
    1 = 1
    AND a.sid <> 01
    AND b.cid IN (SELECT cid FROM sc WHERE sid = 01)
-- 解法2

--  直接筛选
SELECT
    a.sid
FROM
    sc a
WHERE
    a.cid in (SELECT cid FROM sc WHERE sid = '01')
GROUP BY a.sid

-- 套student表子查询
SELECT * FROM student WHERE sid IN (SELECT
    a.sid
FROM
    sc a
WHERE
    1 = 1
    AND a.cid in (SELECT cid FROM sc WHERE sid = '01')
GROUP BY a.sid)
    AND sid <> 01

9、查询和01同学所选课程一样的其他同学

解法没看懂。。。

SELECT  *
FROM student
WHERE student.sid IN(
    SELECT sid
    FROM sc
    WHERE
        1 = 1
        AND sid <> 01
        AND cid IN( SELECT cid FROM sc WHERE sid='01')
        GROUP BY sid HAVING COUNT(*) = (SELECT COUNT(*) FROM sc WHERE sid='01')
)

10、查询没学习过张三老师的任意一门课程的学生姓名

-- 我的解法
-- 1 张三老师ID
SELECT tid
FROM teacher
WHERE tname = '张三'

-- 2 张三老师所授课程ID
SELECT cid
FROM course
WHERE tid = (SELECT tid FROM teacher WHERE tname = '张三')

-- 3 学了该老师的课程的学生ID
SELECT sid
FROM sc
WHERE cid IN(SELECT cid FROM course WHERE tid = (SELECT tid FROM teacher WHERE tname = '张三'))

-- 4、取反
SELECT sname,sid
FROM student
WHERE sid NOT IN (SELECT sid FROM sc WHERE cid IN(SELECT cid FROM course WHERE tid = (SELECT tid FROM teacher WHERE tname = '张三')))

-- 教程解法
-- 查出学习了张三老师课程的学生ID
SELECT
    d.sid
FROM
    sc a
    LEFT JOIN course b ON a.cid = b.cid
    LEFT JOIN teacher c ON b.tid = c.tid
    LEFT JOIN student d ON a.sid = d.sid
WHERE
    1 = 1
    AND c.tname = '张三'
-- 取反
SELECT sname,sid FROM student WHERE sid NOT IN(
    SELECT
        d.sid
    FROM
        sc a
        LEFT JOIN course b ON a.cid = b.cid
        LEFT JOIN teacher c ON b.tid = c.tid
        LEFT JOIN student d ON a.sid = d.sid
    WHERE
        1 = 1
        AND c.tname = '张三'
)

11、查询两门以上不及格的学生

-- 我的解法
-- 1 在sc表直接查出sid和平均成绩【全条件筛查】
SELECT sid, AVG(score) average_score
FROM sc
GROUP BY sid
HAVING
    1 = 1
    AND COUNT(cid) > 1
    AND average_score < 60
-- 2 联表student查即可
SELECT
    A.sname,
    B.*
FROM
    student A,
    (
        SELECT sid, AVG(score) average_score
        FROM sc
        GROUP BY sid
        HAVING
            1 = 1
            AND COUNT(cid) > 1
            AND average_score < 60
    ) B
WHERE
    1 = 1
    AND A.sid = B.sid

-- 教程解法 【语法不支持】
-- 1、先求出两门以上不及格的学生ID
SELECT
    sid
    -- AVG(score) average_score
FROM
    sc
WHERE
    1 = 1
    AND score < 60
GROUP BY sid
HAVING COUNT(1) > 1

-- 求平均值的时候筛选我们要的同学
SELECT
    a.sid,
    b.sname,
    AVG( score )
FROM
    sc a
    LEFT JOIN student b ON a.sid = b.sid
    INNER JOIN ( SELECT sid FROM sc WHERE score < 60 GROUP BY sid HAVING COUNT( 1 ) > 1 ) c ON a.sid = c.sid
GROUP BY
    a.sid

12、查询课程01小于60分，且降序排序的学生信息

-- 我的解法，直接联表
SELECT
    -- a.sid,
    -- a.score,
    b.*
FROM
    sc a,
    student b
WHERE
    1 = 1
    AND a.sid = b.sid
    AND a.score < 60
    AND a.cid = 01
ORDER BY a.score DESC

-- 教程解法，左外连
SELECT
    -- a.sid,
    -- a.score,
    b.*
FROM
    sc a
    LEFT JOIN student b ON a.sid = b.sid
WHERE
    1 = 1
    AND a.score < 60
    AND a.cid = 01
ORDER BY a.score DESC

13、按平均成绩从高到底排序，显示所有学生所有课程成绩和平均成绩

-- 我的解法
-- sc表查询平均成绩和排序操作
SELECT
    sid,
    AVG(score) avg_score
FROM
    sc
GROUP BY
    sid
ORDER BY
    avg_score DESC
-- 因为是所有学生，肯定是学生表做主表查询
SELECT
    a.sid,
    a.sname,
    b.avg_score '平均分',
    (SELECT score FROM sc WHERE sid = a.sid AND cid = 01) '课程01',
    (SELECT score FROM sc WHERE sid = a.sid AND cid = 02) '课程02',
    (SELECT score FROM sc WHERE sid = a.sid AND cid = 03) '课程03'
FROM
    student a
    LEFT JOIN (
        SELECT
        sid,
        AVG(score) avg_score
    FROM
        sc
    GROUP BY
        sid) b
    ON a.sid = b.sid
    ORDER BY
        `平均分` DESC -- 注意，这个排序要从子查询放到外面来

-- 教程解法
SELECT
    a.*,
    avg_score
FROM
    sc a
    LEFT JOIN (SELECT sid, AVG(score) as avg_score FROM sc GROUP BY sid) b
    ON a.sid = b.sid
ORDER BY
    avg_score DESC

14、查询各科成绩最高分，平均分，最低分

按照如下形式：

课程ID，课程名称，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格 >= 60

中等 70 - 80

优良 80 - 90

优秀 >= 90 

还要求选修人数，按这个人数降序排序

人数相同，按课程号升序排序

-- 我的解法
SELECT
    cid,
    (SELECT cname FROM course WHERE cid = a.cid) '课程名称',
    MAX(score) '最高分',
    ROUND(AVG(score), 2) '平均分',
    MIN(score) '最低分',
    (SELECT (SELECT COUNT(1) FROM sc WHERE cid = a.cid AND score > 59 ) / (SELECT COUNT(1) FROM sc WHERE cid = a.cid) ) '及格率',
    (SELECT (SELECT COUNT(1) FROM sc WHERE cid = a.cid AND score BETWEEN 70 AND 79 ) / (SELECT COUNT(1) FROM sc WHERE cid = a.cid) ) '中等率',
    (SELECT (SELECT COUNT(1) FROM sc WHERE cid = a.cid AND score BETWEEN 80 AND 89 ) / (SELECT COUNT(1) FROM sc WHERE cid = a.cid) ) '优良率',
    (SELECT (SELECT COUNT(1) FROM sc WHERE cid = a.cid AND score > 89 ) / (SELECT COUNT(1) FROM sc WHERE cid = a.cid) ) '优秀率',
    (SELECT COUNT(1) FROM sc WHERE cid = a.cid) '选修人数'
FROM
    sc a
GROUP BY
    cid
ORDER BY
    `选修人数` DESC, cid ASC

-- 教程解法
SELECT
    cid,
    -- (SELECT cname FROM course WHERE cid = a.cid) '课程名称',
    MAX(score) '最高分',
    AVG(score) '平均分',
    MIN(score) '最低分',
    SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END) / COUNT(1)  AS '及格率',
    SUM(CASE WHEN score BETWEEN 70 AND 79 THEN 1 ELSE 0 END) / COUNT(1)  AS '中等率',
    SUM(CASE WHEN score BETWEEN 80 AND 89 THEN 1 ELSE 0 END) / COUNT(1)  AS '优良率',
    SUM(CASE WHEN score > 89 THEN 1 ELSE 0 END) / COUNT(1)  AS '优秀率',
    COUNT(1) '选修人数'
FROM
    sc a
GROUP BY
    cid
ORDER BY
    `选修人数` DESC, cid ASC

15、按各科成绩进行排序，显示排名, 成绩重复继续排序

-- 教程解法
SELECT
    sid,
    cid,
    score,
    (@rank:= @rank + 1) rank-- 每查询到一行就赋值给这个记录进行叠加
FROM
    sc, (SELECT @rank:=0) t -- 利用子查询做一张虚拟表，和sc表进行一个笛卡尔积
ORDER BY
    score DESC

16、查询学生的总成绩，进行排名

-- 教程解法
SELECT
    a.*,
    @rank:= if(@sco=scos, '', @rank + 1) rank,
    -- @sco:= scos
FROM
    (SELECT sid, SUM(score) scos FROM sc GROUP BY sid ORDER BY scos DESC) a,
    (SELECT @sco:= NULL, @rank:= 0) b

17、统计各科成绩各分数段人数：

课程编号，课程名称

[100 - 85)

[85 - 70)

[70 - 60)

[60 - 0)

和所占百分比

SELECT
    cid,
    CONCAT( SUM( CASE WHEN score BETWEEN 0 AND 60 THEN 1 ELSE 0 END ) / COUNT( 1 ) * 100, '%' ) '(0 - 60]',
    CONCAT( SUM( CASE WHEN score BETWEEN 61 AND 70 THEN 1 ELSE 0 END ) / COUNT( 1 ) * 100, '%' ) '(60 - 70]',
    CONCAT( SUM( CASE WHEN score BETWEEN 71 AND 85 THEN 1 ELSE 0 END ) / COUNT( 1 ) * 100, '%' ) '(70 - 85]',
    CONCAT( SUM( CASE WHEN score BETWEEN 86 AND 100 THEN 1 ELSE 0 END ) / COUNT( 1 ) * 100, '%' ) '(85 - 100]'
FROM
    sc
GROUP BY
    cid

补充其他字段：

SELECT
    cid,
    (SELECT cname FROM course WHERE cid = sc.cid) '课程',
    CONCAT( SUM( CASE WHEN score BETWEEN 0 AND 60 THEN 1 ELSE 0 END ) / COUNT( 1 ) * 100, '%' ) '(0 - 60]',
    SUM( CASE WHEN score BETWEEN 0 AND 60 THEN 1 ELSE 0 END ) 'P1',
    CONCAT( SUM( CASE WHEN score BETWEEN 61 AND 70 THEN 1 ELSE 0 END ) / COUNT( 1 ) * 100, '%' ) '(60 - 70]',
    SUM( CASE WHEN score BETWEEN 61 AND 70 THEN 1 ELSE 0 END ) 'P2',
    CONCAT( SUM( CASE WHEN score BETWEEN 71 AND 85 THEN 1 ELSE 0 END ) / COUNT( 1 ) * 100, '%' ) '(70 - 85]',
    SUM( CASE WHEN score BETWEEN 71 AND 85 THEN 1 ELSE 0 END ) 'P3',
    CONCAT( SUM( CASE WHEN score BETWEEN 86 AND 100 THEN 1 ELSE 0 END ) / COUNT( 1 ) * 100, '%' ) '(85 - 100]',
    SUM( CASE WHEN score BETWEEN 86 AND 100 THEN 1 ELSE 0 END ) 'P4'
FROM
    sc
GROUP BY
    cid

18、查询各科前三名记录

SELECT
    *
FROM
    sc a
WHERE
    ( SELECT count( 1 ) FROM sc b WHERE a.cid = b.cid AND a.score <= b.score ) < 4
ORDER BY
    a.cid,
    a.score DESC;

19、查询每门课程被选修的学生人数

SELECT
    cid,
    COUNT(1) '选修人数'
FROM
    sc
GROUP BY cid

20、查询只是选修两门课程的学生学号和姓名

-- 我的解法
SELECT
    sid,
    (SELECT sname FROM student WHERE sid = sc.sid) 'name'
FROM
    sc
GROUP BY sid
HAVING COUNT(cid) = 2

-- 教程解法
SELECT
    a.sid,
    b.sname,
    count(1) 'count'
FROM
    sc a
    LEFT JOIN student b ON a.sid = b.sid
    GROUP BY a.sid, b.sname
    HAVING COUNT(a.cid) = 2

21、查询男女人数

SELECT ssex,COUNT(1) 'count' FROM student GROUP BY ssex

22、查询名字中含有【风】的学生信息

SELECT * FROM student WHERE sname LIKE "%风%"

23、查询同名同性学生并且统计人数

-- 我的解法
SELECT
    a.sname,
    a.ssex,
    COUNT(1) 'count'
FROM
    student a,
    student b
WHERE
    1 = 1
    AND a.sid <> b.sid
    AND a.sname = b.sname
    AND a.ssex = b.ssex
GROUP BY a.sname, a.ssex

-- 教程解法
SELECT
    a.sname,
    a.ssex,
    COUNT(1) 'count'
FROM
    student a
    INNER JOIN student b ON a.sid <> b.sid
    AND a.sname = b.sname
    AND a.ssex = b.ssex
GROUP BY a.sname, a.ssex

24、查询1990年出生的学生名单

SELECT
    *
FROM
    student
WHERE
    YEAR(sage) = 1990

25、查询每门课程的平均成绩，按平均成绩降序排列，如果相同，按编号升序排列

SELECT
    cid,
    AVG(score) avg_score
FROM
    sc
GROUP BY cid
ORDER BY avg_score DESC, cid ASC

26、平均成绩大于等于85的所有学生学号，姓名，平均成绩

-- 我的解法
SELECT
    sid,
    (SELECT sname FROM student WHERE sid = sc.sid) 'name',
    AVG(score) avg_score
FROM
    sc
GROUP BY sid
HAVING avg_score > 84


-- 教程解法
SELECT
    a.sid,
    b.sname,
    a.avg_score
FROM
    (SELECT sid, AVG(score) avg_score FROM sc GROUP BY sid HAVING avg_score >= 85) a
    LEFT JOIN student b ON a.sid = b.sid

27、查询课程为数学，且分数低于60的学生姓名和分数

-- 我的解法
-- 1、筛查数学课程ID
SELECT cid FROM course WHERE cname = '数学'
-- 2 列查询 + 子查询 完成
SELECT
    (SELECT sname FROM student WHERE sid = sc.sid) 'name',
    score
FROM sc
WHERE
    1 = 1
    AND cid = (SELECT cid FROM course WHERE cname = '数学')
    AND score < 60

-- 教程解法
SELECT
    a.sid,
    b.sname,
    a.cid,
    a.score
FROM
    sc a
    LEFT JOIN student b ON a.sid = b.sid
WHERE
    1 = 1
    AND cid = (SELECT cid FROM course WHERE cname = '数学')
    AND score < 60

28、查询所有学生课程及分数情况【没有则NULL】

SELECT
    a.sid,
    a.sname,
    b.cid,
    b.score
FROM
    student a
    LEFT JOIN sc b ON a.sid = b.sid

29、查询任意一门课程成绩在70分以上的学生名称，课程名称，分数

-- 我的解法
SELECT
    (SELECT sname FROM student WHERE sid = sc.sid) 'name',
    (SELECT cname FROM course WHERE cid = sc.cid) 'course',
    score
FROM
    sc
WHERE score > 69

-- 教程解法
SELECT
    a.score,
    s.sname,
    c.cname
FROM
    sc a
    LEFT JOIN course c ON a.cid = c.cid
    LEFT JOIN student s ON a.sid = s.sid
WHERE a.score > 69

30、查询存在不及格成绩的课程

-- 我的解法
SELECT
    DISTINCT cid
FROM
    sc
WHERE
    score < 60
-- 教程解法
SELECT
    cid, score
FROM
    sc
WHERE
    score < 60

31、课程01成绩80分以上的学生ID和姓名

-- 我的解法
-- 1、成绩表筛查SID
SELECT
    sid
FROM
    sc
WHERE
    1 = 1
    AND cid = 01
    AND score > 79
-- 2、联表
SELECT
    a.sid,
    b.sname,
    a.score,
    a.cid
FROM
    sc a
    INNER JOIN student b ON a.sid = b.sid
WHERE
    1 = 1
    AND a.cid = 01
    AND a.score > 79

32、每门课程的学生人数

SELECT
    cid,
    COUNT( 1 )
FROM
    sc
GROUP BY
    cid

33、在成绩不重复的情况下，查询张三老师所授课程的学生中，成绩最高的学生信息和成绩

-- 教程解法
SELECT
    *
FROM
    sc a
    LEFT JOIN student b ON a.sid = b.sid
    LEFT JOIN course c ON a.cid = c.cid
    LEFT JOIN teacher d ON c.tid = d.tid
WHERE
    1 = 1
    AND d.tname = '张三'
ORDER BY a.score DESC
LIMIT 1

34、在成绩重复的情况下，查询张三老师所授课程的学生中，成绩最高的学生信息和成绩

-- 我的解法

-- 1、张三老师
SELECT tid FROM teacher WHERE tname = '张三'
-- 2、所受课程【可能不止一门】
SELECT cid FROM course WHERE tid = (SELECT tid FROM teacher WHERE tname = '张三')
-- 3、学习的学生
SELECT * FROM sc WHERE cid IN (SELECT cid FROM course WHERE tid = (SELECT tid FROM teacher WHERE tname = '张三'))
-- 4、取最高成绩和ID
SELECT * FROM sc WHERE cid IN (SELECT cid FROM course WHERE tid = (SELECT tid FROM teacher WHERE tname = '张三')) ORDER BY score DESC LIMIT 1
-- 5、连表 
SELECT
    a.*,
    b.cid,
    (SELECT cname FROM course WHERE cid = b.cid),
    b.score
FROM
    student a,
    (SELECT * FROM sc WHERE cid IN (SELECT cid FROM course WHERE tid = (SELECT tid FROM teacher WHERE tname = '张三')) ORDER BY score DESC LIMIT 1 ) b
WHERE
    1 = 1
    AND a.sid = b.sid

-- 教程解法
SELECT
    s.*
FROM
(SELECT
    a.*,
    CASE
        WHEN @score=score THEN @rank
        WHEN @score:=score THEN @rank:=@rank+1
    END rn
FROM
(SELECT
    a.sid,
    a.score,
    b.sname,
    c.cid,
    d.tname
FROM
    sc a
    LEFT JOIN student b ON a.sid = b.sid
    LEFT JOIN course c ON a.cid = c.cid
    LEFT JOIN teacher d ON c.tid = d.tid
WHERE
    1 = 1
    AND d.tname = '张三') a,
(SELECT @score:=NULL, @rank:=0) t ) s
WHERE rn = 1

35、查询不同课程相同成绩的学生编号，课程编号，学生成绩

-- 我的解法
SELECT
    DISTINCT a.sid,
    a.cid,
    a.score
FROM
    sc a,
    sc b
WHERE
    1 = 1
    AND a.sid = b.sid
    AND a.score = b.score
    AND a.cid <> b.cid

-- 教程解法
SELECT
    a.sid,
    a.cid
FROM
    sc a
    INNER JOIN sc b ON a.sid = b.sid
WHERE
    a.score = b.score
    AND a.cid <> b.cid
GROUP BY a.sid, a.cid

36、查询每门课程考试最好的前两名

-- 参考解法 https://blog.csdn.net/weixin_28847323/article/details/113088935
SELECT
    *
FROM
    sc a
WHERE
    ( SELECT count( 1 ) FROM sc b WHERE a.cid = b.cid AND a.score <= b.score ) < 3
ORDER BY
    a.cid,
    a.score DESC;

-- 教程解法
SELECT
    sid,
    cid,
    score,
    rank
FROM
    (SELECT
        sc.*,
        @rank:=if(@c_cid=cid, if(@sco=score,@rank, @rank + 1), 1) rank,
        @sco:=score,
        @c_cid:=cid
    FROM
        sc,
        (SELECT @sco:=NULL, @rank:=0, @c_cid:=NULL) b
ORDER BY cid, score DESC) a
WHERE a.rank < 3

37、查询每门课程的选修人数【超过五人统计】

SELECT
    cid,
    COUNT(1)
FROM
    sc
GROUP BY
    cid
HAVING
    COUNT(1) > 5

38、查询至少选修两门课程的学生ID　　

-- 我的解法
SELECT
    sid
FROM
    sc
GROUP BY
    sid
HAVING
    COUNT(cid) > 1

-- 教程解法
SELECT
    sid,
    COUNT(1) '选课数'
FROM
    sc
GROUP BY
    sid
HAVING
    COUNT(1) >= 2

39、查询了选修了全部课程的学生信息

-- 我的解法
-- 1、全部课程
SELECT COUNT(1) FROM course
-- 2、全修了课程的sid
SELECT
    sid
FROM
    sc
GROUP BY
    sid
HAVING
    COUNT(cid) = (SELECT COUNT(1) FROM course)
-- 3、做IN条件查询学生表
SELECT * FROM student WHERE sid IN(
SELECT
    sid
FROM
    sc
GROUP BY
    sid
HAVING
    COUNT(cid) = (SELECT COUNT(1) FROM course))

-- 教程解法1 [语法不支持]
SELECT
    b.*,
    a.sid
FROM
    sc a
    INNER JOIN student b ON a.sid = b.sid
GROUP BY
    a.sid
HAVING
    COUNT(1) = (SELECT COUNT(1) FROM course)

-- 教程解法2 [笛卡尔积]
SELECT a.* FROM student a
WHERE (SELECT COUNT(1) FROM sc b WHERE a.sid = b.sid) = (SELECT COUNT(1) FROM COURSE);

40、查询各个学生年龄，只按年份来算

SELECT
    *,
    YEAR(NOW()) - YEAR(sage) age
FROM
    student

41、按照出生年月计算年龄，当前日月 < 出生年月的日月 年龄减一

SELECT
    *,
    TIMESTAMPDIFF(YEAR,sage,NOW()) age
FROM
    student

42、查询本周过生日的学生

SELECT *, WEEK(sage), WEEK(NOW()) FROM student
WHERE WEEK(sage) = WEEK( NOW() )

43、查询下周过生日的学生

SELECT *, WEEK(sage), WEEK(NOW()) FROM student
WHERE WEEK(sage) = WEEK( NOW() ) + 1

44、查询本月过生日的学生

SELECT *, MONTH(sage), MONTH(NOW()) FROM student
WHERE MONTH(sage) = MONTH( NOW() ) + 1

45、查询下个月过生日的学生

SELECT *, MONTH(sage), MONTH(NOW()) FROM student
WHERE MONTH(sage) = MONTH( NOW() ) + 1
05-04 22:19
Powered by LMLPHP ©2024 emdzz 0.080553