HQL必会50题

发表于 更新于 分类于 Waline： 阅读次数：

平常加班不多，为了提高自己的SQL能力，找了网上流传的比较广泛的SQL50题，先把题目拿过来，计划抽空作为练习用HiveSQL完成。

开始日期：2020-03-18

完成日期：2020-03-21

建表语句

1
2
3
4
5
6
7
create table student(s_id string,s_name string,s_birth string,s_sex string) row format delimited fields terminated by '\t';

create table course(c_id string,c_name string,t_id string) row format delimited fields terminated by '\t';

create table teacher(t_id string,t_name string) row format delimited fields terminated by '\t';

create table score(s_id string,c_id string,s_score int) row format delimited fields terminated by '\t';

数据：

student.txt 

1
2
3
4
5
6
7
8
01	赵雷	1990-01-01	男
02	钱电	1990-12-21	男
03	孙风	1990-05-20	男
04	李云	1990-08-06	男
05	周梅	1991-12-01	女
06	吴兰	1992-03-01	女
07	郑竹	1989-07-01	女
08	王菊	1990-01-20	女

teacher.txt

1
2
3
01	张三
02	李四
03	王五

course.txt

1
2
3
01	语文	02
02	数学	01
03	英语	03

score.txt

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
01	01	80
01	02	90
01	03	99
02	01	70
02	02	60
02	03	80
03	01	80
03	02	80
03	03	80
04	01	50
04	02	30
04	03	20
05	01	76
05	02	87
06	01	31
06	03	34
07	02	89
07	03	98

加载数据：

1
2
3
4
5
6
7
load data local inpath '~/student.txt' into table student;

load data local inpath '~/course.txt' into table course;

load data local inpath '~/teacher.txt' into table teacher;

load data local inpath '~/score.txt' into table score;

表之间的关系如图：

  • 1、查询”01”课程比”02”课程成绩高的学生的信息及课程分数:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT 
        T0.*,
        T1.s_score AS c01,
        T2.s_score as c02
    FROM
        student AS T0,
        (SELECT s_id,s_score FROM score WHERE c_id='01') AS T1,
        (SELECT s_id,s_score FROM score WHERE c_id='02') AS T2
    WHERE T0.s_id=T1.s_id
    AND T0.s_id=T2.s_id
    AND T1.s_score>T2.s_score;

    运行结果：

  • 2、查询同时存在” 01 “课程和” 02 “课程的情况:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SELECT
        t3.*,
        t1.s_score as c01,
        t2.s_score as c02
    FROM 
        (SELECT s_id,s_score FROM score WHERE c_id='01') as t1, -- 选修01课程的同学
        (SELECT s_id,s_score FROM score WHERE c_id='02') as t2, -- 选修01课程的同学
        student as t3
    WHERE t1.s_id=t2.s_id
    AND t2.s_id=t3.s_id;

    运行结果：

  • 3、查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null )

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SELECT
        t2.*,
        t1.s_score AS c01,
        t3.s_score AS c02 
    FROM
        (SELECT s_id,s_score FROM score WHERE c_id='01') AS t1, -- 选修01课程的同学
        student AS t2
    LEFT JOIN (SELECT s_id,s_score FROM score WHERE c_id='02') AS t3 -- 选修01课程的同学
    ON t2.s_id = t3.s_id
    WHERE t1.s_id=t2.s_id;

运行结果：

  • 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩:
    (包括有成绩的和无成绩的)
1
2
3
4
5
6
7
8
9
10
11
12
SELECT
    T1.s_id,
    T1.s_name,
    T2.avg_sc
FROM student AS T1
JOIN(SELECT
        s_id,
        avg(s_score) AS avg_sc 
    FROM score
    GROUP BY s_id
    having avg_sc<60) AS T2
ON T1.s_id=T2.s_id;

运行结果：

  • 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SELECT
        T1.s_id,
        T1.s_name,
        T2.co_cnt,
        T3.sum_score
    FROM student AS T1
    LEFT JOIN (SELECT s_id,count(1) AS co_cnt FROM score GROUP BY s_id) AS T2
    ON T1.s_id=T2.s_id
    LEFT JOIN (SELECT s_id,sum(s_score) AS sum_score FROM score GROUP BY s_id) AS T3
    ON T1.s_id=T3.s_id;

    运行结果：

  • 6、查询”李”姓老师的数量:

    1
    SELECT COUNT(1) FROM teacher WHERE t_name LIKE "李%";

  • 7、查询学过”张三”老师授课的同学的信息:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    SELECT 
        T1.*,
        T3.c_name,
        T4.t_name
    FROM
        student AS T1,
        score AS T2,
        course AS T3,
        teacher AS T4
    WHERE T1.s_id=T2.s_id
    AND T2.c_id=T3.c_id
    AND T3.t_id=T4.t_id
    AND T4.t_name="张三";

    运行结果：

  • 8、查询没学过”张三”老师授课的同学的信息:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    SELECT A1.* FROM student AS A1
    LEFT JOIN(SELECT 
                    T1.s_id
                FROM
                    student AS T1,
                    score AS T2,
                    course AS T3,
                    teacher AS T4
                WHERE T1.s_id=T2.s_id
                AND T2.c_id=T3.c_id
                AND T3.t_id=T4.t_id
                AND T4.t_name="张三") AS A2
    ON A1.s_id=A2.s_id
    WHERE A2.s_id IS NULL;

    运行结果：

  • 9、查询学过编号为”01”并且也学过编号为”02”的课程的同学的信息:

    1
    2
    3
    4
    5
    6
    7
    8
    SELECT A.* 
    FROM student AS A
    JOIN (SELECT
                T1.s_id
            FROM (SELECT s_id FROM score WHERE c_id='01') AS T1
            JOIN (SELECT s_id FROM score WHERE c_id='02') AS T2
            ON T1.s_id=T2.s_id) AS B
    ON A.s_id=B.s_id;

    运行结果：

  • 10、查询学过编号为”01”但是没有学过编号为”02”的课程的同学的信息:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    SELECT A.* 
    FROM student AS A
    JOIN (SELECT
                T1.s_id
            FROM (SELECT s_id FROM score WHERE c_id='01') AS T1
            LEFT JOIN (SELECT s_id FROM score WHERE c_id='02') AS T2
            ON T1.s_id=T2.s_id
            WHERE T2.s_id IS NULL) AS B
    ON A.s_id=B.s_id;

运行结果：

  • 11、查询没有学全所有课程的同学的信息:
1
2
3
4
5
6
7
SELECT
    T1.*,
    T2.CNT_C
FROM student AS T1
LEFT JOIN (SELECT s_id,count(1) AS CNT_C FROM score GROUP BY s_id) AS T2
ON T1.s_id=T2.s_id
WHERE T2.CNT_C<3;
  • 12、查询至少有一门课与学号为”01”的同学所学相同的同学的信息:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT 
    t3.*,
    t4.cnt 
FROM student AS t3 
JOIN(
    SELECT s_id,
    COUNT(1) AS cnt 
    FROM score AS t1 
    JOIN (SELECT c_id 
          FROM score
          WHERE s_id ="01") AS t2 
    ON t1.c_id = t2.c_id 
    GROUP BY s_id 
    HAVING cnt>0) AS t4 
ON t3.s_id=t4.s_id;
  • 13、查询和”01”号的同学学习的课程完全相同的其他同学的信息:
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
    A1.*,
    A2.CNT_C
FROM student AS A1
JOIN (SELECT T1.s_id,count(1) AS CNT_C
        FROM score AS T1
        JOIN (SELECT c_id FROM score WHERE s_id='01') AS T2
        ON T1.c_id=T2.c_id
        GROUP BY T1.s_id) AS A2
ON A1.s_id=A2.s_id
AND A1.S_id<>'01'
JOIN (SELECT count(1) AS CNT_C FROM score where s_id='01') AS A3
ON A2.CNT_C=A3.CNT_C;
  • 14、查询没学过”张三”老师讲授的任一门课程的学生姓名:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT S1.s_name 
FROM student AS S1
LEFT JOIN (SELECT
                A1.s_id
            FROM score AS A1
            JOIN (SELECT T2.c_id 
                    FROM teacher AS T1 
                    JOIN course AS T2
                    ON T1.t_id=T2.t_id
                    WHERE T1.t_name='张三') AS A2
            ON A1.c_id=A2.c_id
            GROUP BY A1.s_id) AS S2
ON S1.s_id=S2.s_id
WHERE S2.s_id IS NULL;
  • 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT
    T1.s_id,
    T1.s_name,
    T3.AVG_C
FROM student AS T1
JOIN (SELECT
        s_id,
        count(1) AS CNT_C
    FROM score
    WHERE s_score<60
    GROUP BY s_id
    having CNT_C>=2) AS T2
ON T1.s_id=T2.s_id
JOIN (SELECT
            s_id,
            AVG(s_score) AS AVG_C
        FROM score
        GROUP BY s_id) AS T3
ON T1.s_id=T3.s_id;
  • 16、检索”01”课程分数小于60，按分数降序排列的学生信息:
1
2
3
4
5
6
7
8
9
10
11
12
SELECT
    T1.*,
    T2.s_score
FROM student AS T1
JOIN (SELECT
            s_id,
            s_score
        FROM score
        WHERE c_id='01'
        AND s_score<60) AS T2
ON T1.S_id=T2.s_id
ORDER BY T2.s_score DESC;

运行结果：

  • 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩:

尝试用WITH的写法，相对常规写法逻辑还是比较清晰的。

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
WITH T1 AS (
-- 平均分
    SELECT
        s_id,
        AVG(s_score) AS AVG_S
    FROM score
    GROUP BY s_id
), T2 AS(
-- 语文分数
    SELECT
        s_id,
        s_score
    FROM score
    WHERE c_id='01'
)
, T3 AS(
-- 数学分数
    SELECT
        s_id,
        s_score
    FROM score
    WHERE c_id='02'
), T4 AS(
-- 英语分数
    SELECT
        s_id,
        s_score
    FROM score
    WHERE c_id='03'
)
SELECT
    T0.s_id,
    T0.s_name,
    (CASE WHEN T1.AVG_S IS NULL THEN '0' ELSE round(T1.AVG_S,2) END) AS AVG_S,
    (CASE WHEN T2.s_score IS NULL THEN '0' ELSE T2.s_score END) AS CHINESE,
    (CASE WHEN T3.s_score IS NULL THEN '0' ELSE T3.s_score END) AS MATH,
    (CASE WHEN T4.s_score IS NULL THEN '0' ELSE T4.s_score END) AS ENGLISH
FROM student AS T0
LEFT JOIN T1
ON T1.s_id=T0.s_id
LEFT JOIN T2
ON T2.s_id=T0.s_id
LEFT JOIN T3
ON T3.s_id=T0.s_id
LEFT JOIN T4
ON T4.s_id=T0.s_id
ORDER BY AVG_S DESC;
  • 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分， ，平均分，及格率，中等率，优良率，优秀率: （及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90）
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
WITH A1 AS(
    SELECT
        c_id,
        MAX(s_score) AS max_c,
        MIN(s_score) AS min_c,
        ROUND(AVG(s_score),2)  AS avg_c,
        ROUND(SUM(CASE WHEN s_score>=60 THEN 1 ELSE 0 END)/count(s_score),2) AS L1,
        ROUND(SUM(CASE WHEN s_score BETWEEN 70 AND 79 THEN 1 ELSE 0 END)/count(s_score),2)  AS L2,
        ROUND(SUM(CASE WHEN s_score BETWEEN 80 AND 89 THEN 1 ELSE 0 END)/count(s_score),2)  AS L3,
        ROUND(SUM(CASE WHEN s_score>=90 THEN 1 ELSE 0 END)/count(s_score),2)  AS L4
    FROM score GROUP BY c_id
)
SELECT
    A0.c_id,
    A0.c_name,
    A1.max_c,
    A1.min_c,
    A1.avg_c,
    A1.L1,
    A1.L2,
    A1.L3,
    A1.L4
FROM course AS A0
JOIN A1
ON A0.c_id=A1.c_id;
  • 19、按各科成绩进行排序，并显示排名:
1
2
3
4
5
6
7
8
9
10
11
12
SELECT
    T1.s_id,
    T3.s_name,
    T1.c_id,
    T2.c_name,
    T1.s_score,
    RANK() OVER (PARTITION BY T1.c_id ORDER BY T1.s_score DESC)
FROM score AS T1
JOIN course AS T2
ON T1.c_id=T2.c_id
JOIN student AS T3
ON T1.s_id=T3.s_id;
  • 20、查询学生的总成绩并进行排名:
1
2
3
4
5
6
7
8
9
SELECT
    T1.s_id,
    T3.s_name,
    sum(T1.s_score) AS SUM_C,
    RANK() OVER(ORDER BY sum(T1.s_score) DESC)
FROM score AS T1
JOIN student AS T3
ON T1.s_id=T3.s_id
GROUP BY T1.s_id,T3.s_name;
  • 21、查询不同老师所教不同课程平均分从高到低显示:
1
2
3
4
5
6
7
8
9
10
11
SELECT
    T1.c_id,
    T3.t_name,
    ROUND(AVG(T1.s_score),2),
    RANK() OVER(ORDER BY AVG(T1.s_score) DESC)
FROM score AS T1
JOIN course AS T2
ON T1.c_id=T2.c_id
JOIN teacher AS T3
ON T2.t_id=T3.t_id
GROUP BY T1.c_id,T3.t_name;
  • 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
WITH A1 AS(
    SELECT
        T1.s_id,
        T3.s_name,
        T1.c_id,
        T2.c_name,
        T1.s_score,
        RANK() OVER (PARTITION BY T1.c_id ORDER BY T1.s_score DESC) AS rk
    FROM score AS T1
    JOIN course AS T2
    ON T1.c_id=T2.c_id
    JOIN student AS T3
    ON T1.s_id=T3.s_id
)
SELECT A1.*
FROM A1
WHERE A1.rk=2 OR A1.rk=3;
  • 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
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
WITH A1 AS(
    SELECT
        c_id,
        SUM(CASE WHEN s_score BETWEEN 0 AND 59 THEN 1 ELSE 0 END) AS L0, -- [0-59分的人数]
        ROUND(SUM(CASE WHEN s_score BETWEEN 0 AND 59 THEN 1 ELSE 0 END)/COUNT(1),2) AS H0, -- [0-59分的人数占比]
        SUM(CASE WHEN s_score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS L1, -- [60-69分的人数]
        ROUND(SUM(CASE WHEN s_score BETWEEN 60 AND 69 THEN 1 ELSE 0 END)/COUNT(1),2) AS H1, -- [60-69分的人数占比]
        SUM(CASE WHEN s_score BETWEEN 70 AND 85 THEN 1 ELSE 0 END)  AS L2, -- [70-85分的人数]
        ROUND(SUM(CASE WHEN s_score BETWEEN 70 AND 85 THEN 1 ELSE 0 END)/COUNT(1),2) AS H2, -- [70-85分的人数占比]
        SUM(CASE WHEN s_score BETWEEN 86 AND 100 THEN 1 ELSE 0 END)  AS L3, -- [86-100分的人数]
        ROUND(SUM(CASE WHEN s_score BETWEEN 86 AND 100 THEN 1 ELSE 0 END)/COUNT(1),2) AS H3 -- [86-100分的人数占比]
    FROM score GROUP BY c_id
)
SELECT
    A0.c_id,
    A0.c_name,
    A1.L3,
    A1.H3,
    A1.L2,
    A1.H2,
    A1.L1,
    A1.H1,
    A1.L0,
    A1.H0
FROM course AS A0
JOIN A1
ON A0.c_id=A1.c_id;
  • 24、查询学生平均成绩及其名次:
1
2
3
4
5
6
7
8
9
SELECT
    T1.s_id,
    T3.s_name,
    ROUND(AVG(T1.s_score),2) AS AVG_C,
    RANK() OVER(ORDER BY sum(T1.s_score) DESC)
FROM score AS T1
JOIN student AS T3
ON T1.s_id=T3.s_id
GROUP BY T1.s_id,T3.s_name;
  • 25、查询各科成绩前三名的记录
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
WITH A1 AS(
    SELECT
        T1.s_id,
        T3.s_name,
        T1.c_id,
        T2.c_name,
        T1.s_score,
        RANK() OVER (PARTITION BY T1.c_id ORDER BY T1.s_score DESC) AS rk
    FROM score AS T1
    JOIN course AS T2
    ON T1.c_id=T2.c_id
    JOIN student AS T3
    ON T1.s_id=T3.s_id
)
SELECT A1.*
FROM A1
WHERE A1.rk<=3;
  • 26、查询每门课程被选修的学生数:
1
2
3
4
5
6
7
8
SELECT
    T1.c_id,
    T2.c_name,
    COUNT(1)
FROM score AS T1
JOIN course AS T2
ON T1.c_id=T2.c_id
GROUP BY T1.c_id,T2.c_name
  • 27、查询出只有两门课程的全部学生的学号和姓名:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH A1 AS(
    SELECT
        T1.s_id,
        COUNT(1) AS CNT
    FROM score AS T1
    GROUP BY T1.s_id
    HAVING CNT=2
)
SELECT
    A2.s_id,
    A2.s_name,
    A1.CNT
FROM student AS A2
JOIN A1 
ON A1.s_id=A2.s_id;
  • 28、查询男生、女生人数:
1
2
3
4
5
SELECT
    T1.s_sex,
    COUNT(1)
FROM student AS T1
GROUP BY s_sex;
  • 29、查询名字中含有”风”字的学生信息:
1
2
3
4
SELECT
    T1.*
FROM student AS T1
WHERE T1.s_name LIKE "%风%"
  • 30、查询同名同性学生名单，并统计同名人数:

数据中发现没有同名同性的学生，先插入几条

1
2
3
4
5
6
insert into student values
('09' , '张三' , '2017-12-20' , '女'),
('10' , '李四' , '2017-12-25' , '女'),
('11' , '李四' , '2012-06-06' , '女'),
('12' , '赵六' , '2013-06-13' , '女'),
('13' , '孙七' , '2014-06-01' , '女');

然后找同名同性的学生

1
2
3
4
5
6
7
SELECT
    s_name,
    s_sex,
    COUNT(1) AS CNT_N
FROM student
GROUP BY s_name,s_sex
HAVING CNT_N>1;
  • 31、查询1990年出生的学生名单:
1
SELECT * FROM student WHERE s_birth BETWEEN '1990-01-01' AND '1990-12-31';
  • 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH T1 AS (
    SELECT
        c_id,
        ROUND(AVG(s_score),2) AS AVG_C
    FROM score
    GROUP BY c_id
)
SELECT
    T1.c_id,
    T0.c_name,
    T1.AVG_C
FROM course AS T0
JOIN T1
ON T0.c_id=T1.c_id
ORDER BY T1.AVG_C DESC,T1.c_id;
  • 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH T1 AS (
    SELECT
        s_id,
        AVG(s_score) AS AVG_S
    FROM score
    GROUP BY s_id
    HAVING AVG_S>=85
)
SELECT
    T0.s_id,
    T0.s_name,
    T1.AVG_S
FROM student as T0
JOIN T1
ON T0.s_id=T1.s_id;
  • 34、查询课程名称为”数学”，且分数低于60的学生姓名和分数:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH T1 AS (
    SELECT
        s_id,
        AVG(s_score) AS AVG_S
    FROM score
    GROUP BY s_id
    HAVING AVG_S>=85
)
SELECT
    T0.s_id,
    T0.s_name,
    ROUND(T1.AVG_S,2) AS AVG_S
FROM student as T0
JOIN T1
ON T0.s_id=T1.s_id;
  • 35、查询所有学生的课程及分数情况:
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
WITH T1 AS (
    SELECT 
        s_id,
        s_score
    FROM score
    WHERE c_id='01'
), T2 AS (
    SELECT 
        s_id,
        s_score
    FROM score
    WHERE c_id='02'
), T3 AS (
    SELECT 
        s_id,
        s_score
    FROM score
    WHERE c_id='03'
)
SELECT
    T0.s_id,
    T0.s_name,
    (CASE WHEN T1.s_score IS NULL THEN "未选修" ELSE T1.s_score END) AS CHINESE,
    (CASE WHEN T2.s_score IS NULL THEN "未选修" ELSE T2.s_score END) AS MATH,
    (CASE WHEN T3.s_score IS NULL THEN "未选修" ELSE T3.s_score END) AS ENGLISH
FROM student AS T0
LEFT JOIN T1
ON T0.s_id=T1.s_id
LEFT JOIN T2
ON T0.s_id=T2.s_id
LEFT JOIN T3
ON T0.s_id=T3.s_id;
  • 36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数:
1
2
3
4
5
6
7
8
9
10
11
12
SELECT
    -- T0.s_id,
    T1.s_name,
    -- T0.c_id,
    T2.c_name,
    T0.s_score
FROM score AS T0
LEFT JOIN student AS T1
ON T0.s_id=T1.s_id
LEFT JOIN course AS T2
ON T0.c_id=T2.c_id
WHERE T0.s_score>70;
  • 37、查询课程不及格的学生:
1
2
3
4
5
6
7
8
9
10
11
12
SELECT
    -- T0.s_id,
    T1.s_name,
    -- T0.c_id,
    T2.c_name,
    T0.s_score
FROM score AS T0
LEFT JOIN student AS T1
ON T0.s_id=T1.s_id
LEFT JOIN course AS T2
ON T0.c_id=T2.c_id
WHERE T0.s_score<60;
  • 38、查询课程编号为01且课程成绩在80分及以上的学生的学号和姓名:
1
2
3
4
5
6
7
8
SELECT
    T1.s_id,
    T2.s_name
FROM score AS T1
JOIN student AS T2
ON T1.s_id=T2.s_id
WHERE T1.c_id='01'
AND T1.s_score>=80;
  • 39、求每门课程的学生人数:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH T1 AS (
    SELECT
        c_id,
        COUNT(1) AS CNT_S
    FROM score
    GROUP BY c_id
)
SELECT
    T2.c_id,
    T2.c_name,
    T1.CNT_S
FROM course AS T2
JOIN T1
ON T1.c_id=T2.c_id;
  • 40、查询选修”张三”老师所授课程的学生中，成绩最高的学生信息及其成绩:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
WITH T1 AS(
    SELECT
        A4.*,
        A3.s_score,
        RANK() OVER(ORDER BY A3.s_score DESC) AS RK
    FROM teacher AS A1
    JOIN course AS A2
    ON A1.t_id=A2.t_id
    JOIN score AS A3
    ON A3.c_id=A2.c_id
    JOIN student AS A4
    ON A3.s_id=A4.s_id
    WHERE A1.t_name='张三'
)
SELECT
    T1.*
FROM T1
WHERE RK=1;
  • 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩:
1
2
3
4
5
6
7
8
9
SELECT 
    DISTINCT T1.s_id,
    T1.c_id,
    T3.c_name,
    T1.s_score
FROM score AS T1,score AS T2,course AS T3
WHERE T1.c_id <> T2.c_id 
AND T1.s_score=T2.s_score
AND T1.c_id=T3.c_id;
  • 42、查询每门课程成绩最好的前三名:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH A1 AS(
    SELECT
        T1.s_id,
        T1.c_id,
        T1.s_score,
        RANK() OVER(PARTITION BY T1.c_id ORDER BY T1.s_score DESC) AS RK
    FROM score AS T1
)
SELECT
    A1.s_id,
    A3.s_name,
    A2.c_name,
    A1.s_score,
    A1.RK
FROM A1
JOIN course AS A2
ON A1.c_id=A2.c_id
JOIN student AS A3
ON A1.s_id=A3.s_id
WHERE RK<=3;
  • 43、统计每门课程的学生选修人数（超过5人的课程才统计）:要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
1
2
3
4
5
6
7
SELECT
    c_id,
    COUNT(1) AS CNT_C
FROM score
GROUP BY c_id
HAVING CNT_C>=5
ORDER BY CNT_C DESC,c_id;
  • 44、检索至少选修两门课程的学生学号:
1
2
3
4
5
6
SELECT
    s_id,
    COUNT(1) AS CNT_S
FROM score
GROUP BY s_id
HAVING CNT_S>=2
  • 45、查询选修了全部课程的学生信息:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH A1 AS(
    SELECT
        T1.s_id,
        COUNT(1) AS CNT_S
    FROM score AS T1
    GROUP BY T1.s_id
    HAVING CNT_S=3
)
SELECT
    A0.*
FROM student A0
JOIN A1
ON A0.s_id=A1.s_id
WHERE A1.CNT_S=3;
  • 46、查询各学生的年龄(周岁):
1
2
3
4
5
6
7
8
9
10
SELECT
    s_name,
    s_birth,
    (YEAR(current_date())-YEAR(s_birth)-
        (CASE WHEN MONTH(current_date())>MONTH(s_birth) THEN 0 
              WHEN MONTH(current_date())=MONTH(s_birth) THEN 
                    (CASE WHEN DAY(current_date())>=DAY(s_birth) THEN 0 ELSE 1 END)
          ELSE 1 END)
    ) AS AGE
FROM student;
  • 47、查询本周过生日的学生:
1
2
3
4
5
SELECT
    s_name,
    s_birth
FROM student
WHERE s_birth BETWEEN DATE_ADD(NEXT_DAY(current_date(), 'MON'),-1) AND DATE_ADD(NEXT_DAY(current_date(), 'MON'),-7);

今天是3月21号，暂时没有本周过生日的同学，不过本题的思想是找到本周第一天和本周最后一天，HQL实现如下：

1
2
3
SELECT
        DATE_ADD(NEXT_DAY(current_date(), 'MON'),-1) AS SUNDAY,
        DATE_ADD(NEXT_DAY(current_date(), 'MON'),-7) AS MONDAY
  • 48、查询下周过生日的学生:
1
2
3
4
5
SELECT
    s_name,
    s_birth
FROM student
WHERE s_birth BETWEEN NEXT_DAY(current_date(), 'MON') AND DATE_ADD(NEXT_DAY(current_date(), 'MON'),6);

同样没有下周过生日的同学，下周一到下周日的HQL如下：

1
SELECT NEXT_DAY(current_date(), 'MON') , DATE_ADD(NEXT_DAY(current_date(), 'MON'),6)
  • 49、查询本月过生日的学生:
1
2
3
4
5
SELECT
    s_name,
    s_birth
FROM student
WHERE MONTH(s_birth)=MONTH(current_date());
  • 50、查询12月份过生日的学生:
1
2
3
4
5
SELECT
    s_name,
    s_birth
FROM student
WHERE MONTH(s_birth)=12;