sql经典50题

一、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）
一刷：
excel思路：
观察原始表格数据，在excel中想得到01比02高，我们需要将原始表格拆分成两个表，课程01表和课程02表，再进行vlookup得到c表，根据if条件判断筛出最终数据。
重点是：1.拆表 2.匹配
转成SQL语言：
1.拆表语言：

(select * from score where s_id = '01') as a
(select * from score where s_id = '02') as b

2.关联加匹配语言：

(select * from score where s_id = '01') as a 
inner join 
(select * from score where s_id = '02') as b 
on a.s_id = b.s_id and a.s_score > b.s_score

3.完整语言：

select a.s_id, a.s_score as 01, b.s_score as 02 
from 
(select * from score where s_id = '01'）as a 
inner join 
(select * from score where s_id = '02') as b 
on a.s_id = b.s_id and a.s_score > b.s_score

----到这里其实就可以结束了；
4.若想加student的信息，则需要以上所有结果再作为c表再关联匹配：

select st.*,c.a.s_score as 01,c.b.s_score as 02 
from student st 
inner join
(select * from 
(select * from score where c_id = '01') as a inner join 
(select * from score where c_id = '02') as b 
on a.s_id = b.s_id and a.s_score > b.s_score)
 as c 
on st.s_id=c.a.s_id;

左右结构

若想让字段1和2为上下结果，即重复前面的信息，则语言如下：

select st.*,score.c_id,score.s_score 
from student st 
inner join score 
on st.s_id = score.s_id 
where st.s_id in 
(select a.s_id from (select * from score where c_id = '01') as a 
inner join 
(select * from score where score.c_id = '02') as b 
on a.s_id = b.s_id and a.s_score > b.s_score);

上下结构

二刷：

SELECT
    a.s_id,
    a.c_id "01课程",
    a.s_score "01分数",
    b.c_id "02课程",
    b.s_score "02分数" 
FROM
    ( SELECT s_id, c_id, s_score FROM score WHERE c_id = "01" ) a
    INNER JOIN ( SELECT s_id, c_id, s_score FROM score WHERE c_id = "02" ) b ON a.s_id = b.s_id 
    AND a.s_score > b.s_score

image.png

二、查询平均成绩大于60分的学生的学号和平均成绩（简单，第二道重点）
一刷：

SELECT
    s_id,
    round( avg( s_score ), 0 ) AS 平均成绩,
    60 AS 标准 
FROM
    score 
GROUP BY
    s_id 
HAVING
    avg( s_score ) > 60;

扩展一：查询成绩小于60分的学生的学号和姓名
1、先反向找出大于等于60分的学号：

select s_id from score where s_score >= 60

2、匹配：

SELECT
    st.*,
    sc.c_id,
    sc.s_score 
FROM
    student AS st
    INNER JOIN score AS sc ON st.s_id = sc.s_id 
WHERE
    st.s_id NOT IN ( SELECT s_id FROM score WHERE s_score >= 60 );

扩展二：查询平均成绩小于60分的学生的学号、姓名和平均成绩
第一种
1、先找出小于60分和空的作为c表：

SELECT
    s_id,
    round( avg( s_score ), 0 ) AS 平均成绩,
    60 AS 标准 
FROM
    score 
GROUP BY
    s_id 
HAVING
    avg( s_score ) < 60 
    OR avg( s_score ) IS NULL;

2、匹配：

SELECT
    st.*,
    c.* 
FROM
    student AS st
    INNER JOIN (
    SELECT
        s_id,
        round( avg( s_score ), 0 ) AS 平均成绩,
        60 AS 标准 
    FROM
        score 
    GROUP BY
        s_id 
    HAVING
        avg( s_score ) < 60 
    OR avg( s_score ) IS NULL 
    ) AS c ON st.s_id = c.s_id;

三、查询所有学生的学号、姓名、选课数、总成绩（不重要）

SELECT
    st.s_id,
    st.s_name,
    count( sc.c_id ),
    sum( sc.s_score ) 
FROM
    student st
    INNER JOIN score sc ON st.s_id = sc.s_id 
GROUP BY
    st.s_id,
    st.s_name;

四、查询姓“李”的老师的个数（不重要）

SELECT
    t_name,
    t_id,
    count( t_id )
FROM
    teacher 
WHERE
    t_name LIKE "李%"

五、查询没学过“张三”老师课的学生的学号、姓名（重点）

SELECT
    st.s_id,
    st.s_name,
    sc.c_id,
    te.t_id,
    te.t_name 
FROM
    student st
    LEFT JOIN score sc ON st.s_id = sc.s_id
    LEFT JOIN course co ON sc.c_id = co.c_id
    LEFT JOIN teacher te ON co.t_id = te.t_id 
WHERE
    st.s_id NOT IN (
    SELECT
        st.s_id 
    FROM
        student st
        LEFT JOIN score sc ON st.s_id = sc.s_id
        LEFT JOIN course co ON sc.c_id = co.c_id
        LEFT JOIN teacher te ON co.t_id = te.t_id 
    WHERE
    te.t_name = "张三" 
    )

六、查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）

SELECT
    st.s_id,
    st.s_name,
    sc.c_id,
    te.t_id,
    te.t_name 
FROM
    student st
    LEFT JOIN score sc ON st.s_id = sc.s_id
    LEFT JOIN course co ON sc.c_id = co.c_id
    LEFT JOIN teacher te ON co.t_id = te.t_id 
WHERE
    te.t_name = "张三"

七、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（重点）

SELECT
    a.s_id,
    a.s_name,
    a.c_id,
    b.c_id 
FROM
    (
    SELECT
        st.s_id,
        st.s_name,
        sc.c_id 
    FROM
        student st
        LEFT JOIN score sc ON st.s_id = sc.s_id 
    WHERE
        sc.c_id = "01" 
    ) a
    INNER JOIN (
    SELECT
        st.s_id,
        st.s_name,
        sc.c_id 
    FROM
        student st
        LEFT JOIN score sc ON st.s_id = sc.s_id 
    WHERE
    sc.c_id = "02" 
    ) b ON a.s_id = b.s_id

八、查询课程编号为“02”的总成绩（不重点）

SELECT
    c_id,
    sum( s_score ) 
FROM
    score 
WHERE
    c_id = "02"

九、查询成绩小于60分的学生的学号和姓名（同题目二）
十、查询没有学全所有课的学生的学号、姓名(重点)

SELECT
    st.s_id,
    st.s_name,
count( sc.c_id ) 
FROM
    student st
    LEFT JOIN score sc ON st.s_id = sc.s_id 
GROUP BY
    st.s_id,
    st.s_name 
HAVING
    count( sc.c_id ) < ( SELECT count( c_id ) FROM course )

十一、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名（重点）

SELECT
    st.s_id,
    st.s_name,
    sc.c_id 
FROM
    student st
    LEFT JOIN score sc ON st.s_id = sc.s_id 
where
    sc.c_id IN ( SELECT c_id FROM score WHERE s_id = "01" )

十二、查询和“01”号同学所学课程完全相同的其他同学的学号(重点)

SELECT
    a.s_id,
    a.s_name 
FROM
    (
    SELECT
        st.s_id,
        st.s_name,
        sc.c_id 
    FROM
        student st
        LEFT JOIN score sc ON st.s_id = sc.s_id 
    WHERE
        sc.c_id IN ( SELECT c_id FROM score WHERE s_id = "01" ) 
    ) a 
GROUP BY
    a.s_id,
    a.s_name 
HAVING
    count( a.s_id ) = ( SELECT count( c_id ) FROM score WHERE s_id = '01' )

十五、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（重点）

SELECT
    a.s_id,
    a.s_name,
    a.总平均分,
    b.不及格课程数 
FROM
    (
    SELECT
        st.s_id,
        st.s_name,
        round( avg( sc.s_score ), 0 ) AS 总平均分 
    FROM
        student st
        INNER JOIN score sc ON st.s_id = sc.s_id 
    WHERE
        st.s_id IN ( SELECT s_id FROM score WHERE NOT s_score >= 60 GROUP BY s_id HAVING count( c_id ) >= 2 ) 
    GROUP BY
        st.s_id,
        st.s_name 
    ) a
    INNER JOIN (
    SELECT
        st.s_id,
        st.s_name,
        count( sc.c_id ) AS 不及格课程数 
    FROM
        student st
        INNER JOIN score sc ON st.s_id = sc.s_id 
    WHERE
        NOT sc.s_score >= 60 
    GROUP BY
        st.s_id,
        st.s_name 
    HAVING
    count( sc.c_id ) >= 2 
    ) b ON a.s_id = b.s_id;

十六、检索"01"课程分数小于60，按分数降序排列的学生信息（和34题重复，不重点

SELECT
    st.s_id,
    st.s_name,
    sc.s_score 
FROM
    student st
    INNER JOIN score sc ON st.s_id = sc.s_id 
WHERE
    sc.s_score < 60 
    AND sc.c_id = "01" 
ORDER BY
    sc.s_score DESC

十七、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(重重点与35一样)

SELECT
    s_id,
    max( CASE WHEN c_id = '01' THEN s_score ELSE NULL END ) AS 语文,
    max( CASE WHEN c_id = '02' THEN s_score ELSE NULL END ) AS 数学,
    max( CASE WHEN c_id = '03' THEN s_score ELSE NULL END ) AS 英语,
    round( avg( s_score ), 0 ) AS 平均成绩 
FROM
    score 
GROUP BY
    s_id 
ORDER BY
    round( avg( s_score ), 0 ) DESC;

十八、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 (超级重点)

SELECT
    sc.c_id,
    co.c_name,
    round( avg( s_score ) ) AS 平均分,
    max( sc.s_score ) AS 最高分,
    min( sc.s_score ) AS 最低分,
    concat(
        round( sum( CASE WHEN sc.s_score >= 60 THEN 1 ELSE 0 END )*100 / count( sc.s_id ), 2 ),
        '%' 
    ) AS 及格率,
    concat(
        round(
            sum( CASE WHEN sc.s_score >= 70 AND sc.s_score < 80 THEN 1 ELSE 0 END )*100 / count( sc.s_id ),
            2 
        ),
        '%' 
    ) AS 中等率,
    concat(
        round(
            sum( CASE WHEN sc.s_score >= 80 AND sc.s_score < 90 THEN 1 ELSE 0 END )*100 / count( sc.s_id ),
            2 
        ),
        '%' 
    ) AS 优良率,
    concat(
        round( sum( CASE WHEN sc.s_score >= 90 THEN 1 ELSE 0 END )*100 / count( sc.s_id ), 2 ),
        '%' 
    ) AS 优秀率 
FROM
    score sc
    INNER JOIN course co ON sc.c_id = co.c_id 
GROUP BY
    sc.c_id;

十九、按各科成绩进行排序，并显示排名

-- 按各科成绩进行排序，并显示排名(重点row_number)
SELECT
    a.c_id,
    a.s_id,
    a.s_score,
    COUNT( b.s_score ) + 1 AS rank 
FROM
    score a
    LEFT JOIN score b ON a.s_score < b.s_score 
    AND a.c_id = b.c_id 
GROUP BY
    a.c_id,
    a.s_id,
    a.s_score 
ORDER BY
    a.c_id,
    rank ASC;

二十、查询学生的总成绩并进行排名（不重点）

SELECT
    s_id,
    sum( s_score ) 
FROM
    score 
GROUP BY
    s_id 
ORDER BY
    sum( s_score ) DESC;

二十一、查询不同老师所教不同课程平均分从高到低显示(不重点)

SELECT
    sc.c_id,
    co.c_name,
    te.t_id,
    te.t_name,
    avg( sc.s_score ) 
FROM
    score sc
    INNER JOIN course co ON sc.c_id = co.c_id
    INNER JOIN teacher te ON co.t_id = te.t_id 
GROUP BY
    sc.c_id,
    co.c_name,
    te.t_id,
    te.t_name 
ORDER BY
    avg( sc.s_score ) desc

二十二、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（重要 25类似

SELECT
    * 
FROM
    (
        st.*,
        sc.c_id,
        sc.s_score,
        row_number ( ) over (
            PARTITION BY sc.c_id 
        ORDER BY
            sc.s_score DESC AS 名次 
        FROM
            student st
            INNER JOIN score sc ON st.s_id = sc.s_id 
        ) a 
WHERE
    名次 IN ( 2, 3 );

二十三、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称(重点和18题类似)

SELECT
    c.c_id,
    c.c_name,
    sum( CASE WHEN sc.s_score < 60 THEN 1 ELSE NULL END ) AS '[<60]',
    sum( CASE WHEN sc.s_score >= 60 AND sc.s_score < 70 THEN 1 ELSE NULL END ) AS '[70-60]',
    sum( CASE WHEN sc.s_score >= 70 AND sc.s_score < 85 THEN 1 ELSE NULL END ) AS '[85-70]',
    sum( CASE WHEN sc.s_score >= 85 AND sc.s_score < 100 THEN 1 ELSE NULL END ) AS '[100-85]' 
FROM
    course c
    INNER JOIN score sc ON c.c_id = sc.c_id 
GROUP BY
    c.c_id,
    c.c_name;

二十四、查询学生平均成绩及其名次（同19题，重点）

SELECT
    s_id,
    roud ( avg( s_score ), 0 ) 平均分,
    row_number ( ) over ( ORDER BY roud ( avg( s_score ), 0 ) DESC ) 
FROM
    score 
GROUP BY
    s_id;

二十五、查询各科成绩前三名的记录（不考虑成绩并列情况）（重点与22题类似）

SELECT
    * 
FROM
    (
        st.*,
        sc.c_id,
        sc.s_score,
        row_number ( ) over (
            PARTITION BY sc.c_id 
        ORDER BY
            sc.s_score DESC AS 名次 
        FROM
            student st
            INNER JOIN score sc ON st.s_id = sc.s_id 
        ) a 
WHERE
    名次 IN ( 1, 3 );

二十六、查询每门课程被选修的学生数(不重点)

SELECT
    s.c_id,
    c.c_name,
    count( s.c_id )
FROM
    score s
    INNER JOIN course c ON s.c_id = c.c_id 
GROUP BY
    s.c_id,
    c.c_name

二十七、查询出只有两门课程的全部学生的学号和姓名(不重点)

SELECT
    st.s_id,
    st.s_name,
    count( sc.c_id ) 
FROM
    score sc
    INNER JOIN student st ON sc.s_id = st.s_id 
GROUP BY
    st.s_id,
    st.s_name
having
    count( sc.c_id ) = 2

二十八、查询男生、女生人数(不重点)

SELECT s_sex,COUNT(s_sex)
from student
group by s_sex

二十九、查询名字中含有"风"字的学生信息（不重点）

select * from student where s_name like "%风%";

三十一、查询1990年出生的学生名单（重点year）

select * from student where year(s_birth)=1990;

三十二、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩（不重要）

SELECT
    st.s_id,
    st.s_name,
    avg( sc.s_score ) 
FROM
    student st
    INNER JOIN score sc ON st.s_id = sc.s_id 
GROUP BY
    st.s_id,
    st.s_name 
HAVING
    avg( sc.s_score ) >= 85

三十三、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列（不重要）

select c_id,avg(s_score)
from score
group by c_id
order by avg(s_score),c_id

三十四、查询课程名称为"数学"，且分数低于60的学生姓名和分数（不重点）

SELECT
    st.s_name,
    sc.s_score 
FROM
    student st
    INNER JOIN score sc ON st.s_id = sc.s_id
    INNER JOIN course c ON sc.c_id = c.c_id 
WHERE
    c.c_name = "数学" 
    AND sc.s_score < 60;

三十五、查询所有学生的课程及分数情况（重点）

SELECT
    sc.s_id,
    max( CASE WHEN c.c_name = '语文' THEN sc.s_score ELSE NULL END ) '语文',
    max( CASE WHEN c.c_name = '数学' THEN sc.s_score ELSE NULL END ) '数学',
    max( CASE WHEN c.c_name = '英语' THEN sc.s_score ELSE NULL END ) '英语',
    sc.s_score 
FROM
    score sc
    INNER JOIN course c ON sc.c_id = c.c_id 
GROUP BY
    sc.s_id;

三十六、查询任何一门课程成绩在70分以上的姓名、课程名称和分数（重点）

SELECT
    s_name,
    c_id,
    s_score 
FROM
    student
    INNER JOIN score ON student.s_id = score.s_id 
WHERE
    score.s_score > 60

三十七、查询不及格的课程并按课程号从大到小排列(不重点)

SELECT
    s_id,
    c_id,
    s_score 
FROM
    score 
WHERE
    s_score < 60 
ORDER BY
    c_id

三十八、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名（不重要）

SELECT
    s_name,
    student.s_id,
    s_score,
    c_id 
FROM
    student
    INNER JOIN score ON student.s_id = score.s_id 
WHERE
    score.c_id = "03" 
    AND score.s_score > 80

三十九、求每门课程的学生人数（不重要）

SELECT
    c_id,
    count( c_id ) 
FROM
    score 
GROUP BY
    c_id

四十、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩（重要top）

SELECT
    s_name,
    s_score 
FROM
    student
    INNER JOIN score ON student.s_id = score.s_id
    INNER JOIN course ON score.c_id = course.c_id
    INNER JOIN teacher ON course.t_id = teacher.t_id 
WHERE
    teacher.t_name = "张三" 
ORDER BY
    score.s_score DESC 
    LIMIT 0,1

四十一、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩（重点）

SELECT
    a.s_id,
    a.c_id,
    a.s_score,
    b.s_id,
    b.c_id 
FROM
    ( SELECT s_id, c_id, s_score FROM score ) a
    LEFT JOIN ( SELECT s_id, c_id, s_score FROM score ) b ON a.s_score = b.s_score 
WHERE
    a.c_id <> b.c_id 
ORDER BY
    a.s_id,
    a.c_id,
    b.s_id

四十二、查询每门功课成绩最好的前两名（同22和25题）

SELECT
    * 
FROM
    (
        st.*,
        sc.c_id,
        sc.s_score,
        row_number ( ) over (
            PARTITION BY sc.c_id
        ORDER BY
            sc.s_score DESC) AS 名次 
        FROM
            student st
            INNER JOIN score sc ON st.s_id = sc.s_id 
        ) a 
WHERE
    名次 IN ( 1, 2 );

四十三、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列（不重要）

SELECT
    c_id,
    count( c_id ) 
FROM
    score 
GROUP BY
    c_id 
HAVING
    count( c_id ) > 5 
ORDER BY
    count( c_id ) DESC,
    c_id ASC

四十四、检索至少选修两门课程的学生学号（不重要）

SELECT
    s_id,
    count( s_id ) 
FROM
    score 
GROUP BY
    s_id 
HAVING
    count( c_id ) >= 2

四十五、查询选修了全部课程的学生信息（重点划红线地方）

SELECT
    st.s_id,
    st.s_name,
    count( sc.c_id ) 
FROM
    student st
    INNER JOIN score sc ON st.s_id = sc.s_id 
GROUP BY
    st.s_id,
    st.s_name 
HAVING
    count( sc.c_id ) = ( SELECT count( c_id ) FROM course )

四十六、查询各学生的年龄（精确到月份）

select s_id, s_name, concat(
cast(floor(TIMESTAMPDIFF(month, s_birth, now()) / 12) as char),
"岁零", 
cast((TIMESTAMPDIFF(month, s_birth, now()) mod 12) as char),
"个月") as 年龄
from student;

四十七、查询没学过“张三”老师讲授的任一门课程的学生姓名（还可以，自己写的，答案中没有）

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

四十八、查询两门以上不及格课程的同学的学号及其平均成绩

SELECT
    b.s_id,
    c.平均分,
    b.不及格课程数
FROM
    (SELECT
    s_id,
    avg(s_score) as 平均分
FROM
    score 
GROUP BY
    s_id) as c
    right join 
    (SELECT
        a.s_id,
        round(count(a.s_id),2) as 不及格课程数
    FROM
        ( SELECT s_id, s_score FROM score WHERE s_score < 60 ) a 
    GROUP BY
        a.s_id 
    HAVING
    count(a.s_id) >= 2 
    ) as b ON c.s_id = b.s_id

四十九、查询本月过生日的学生（无法使用week、date(now()）

select *
from
student
where month(s_birth) = month(date(now()))

五十、查询下月过生日的学生

select *
from
student
where month(s_birth) = month(date(now()))+1