經(jīng)典SQL練習(xí)題(MySQL版)

網(wǎng)上有一篇關(guān)于SQL的經(jīng)典文章盒件，超經(jīng)典SQL練習(xí)題桌肴，做完這些你的SQL就過關(guān)了，引用和分析它的人很多色鸳，于是今天復(fù)習(xí)SQL的時候找來練了練手社痛。原作者用的是SQL Server 2008，我在這里用的是MySQL 8.0.11（二者語法差別不大）命雀，文本編輯器用的是Atom 1.28.2（不知道大家用什么蒜哀，反正用Atom寫SQL確實絲質(zhì)順滑）。

題目順序和原文一致吏砂，但是我沒有把所有題目都解一遍撵儿，因為很多題目是重復(fù)的乘客。在每道題題目下我除了放SQL語句外，還把MySQL的運行輸出結(jié)果放了上來淀歇，展示效果更直觀一些易核。另外，因為數(shù)據(jù)量非常小浪默，所以就沒考慮SQL語句的性能優(yōu)化牡直，只求順利完成題目，并盡可能寫得簡單些纳决。

開始之前碰逸，先從SQL常見的一些面試題(太有用啦)搬運幾道我認(rèn)為很不錯的經(jīng)典題目過來，這些題目的解法體現(xiàn)出來的方法和思路可以適用于本文的絕大部分題目阔加，是必備的基礎(chǔ)饵史。

1. 用一條SQL 語句查詢出每門課都大于80 分的學(xué)生姓名

name course grade
張三語文 81
張三數(shù)學(xué) 75
李四語文 76
李四數(shù)學(xué) 90
王五語文 81
王五數(shù)學(xué) 100
王五英語 90

select name from table group by name having min(grade) > 80

2. 現(xiàn)有學(xué)生表如下:
自動編號學(xué)號姓名課程編號課程名稱分?jǐn)?shù)
1 2005001 張三 0001 數(shù)學(xué) 69
2 2005002 李四 0001 數(shù)學(xué) 89
3 2005001 張三 0001 數(shù)學(xué) 69
刪除除了自動編號不同, 其他都相同的學(xué)生冗余信息

delete tablename where 自動編號 not in (
    select min( 自動編號) 
    from tablename 
    group by 學(xué)號, 姓名, 課程編號, 課程名稱, 分?jǐn)?shù)
)

3. 一個叫 team 的表，里面只有一個字段name, 一共有4 條紀(jì)錄掸哑，分別是a,b,c,d, 對應(yīng)四個球?qū)υ技保F(xiàn)在四個球?qū)M行比賽，用一條sql 語句顯示所有可能的比賽組合

select a.name, b.name
from team a, team b 
where a.name < b.name

4. 請用SQL 語句實現(xiàn)：從TestDB 數(shù)據(jù)表中查詢出所有月份的發(fā)生額都比101 科目相應(yīng)月份的發(fā)生額高的科目苗分。
請注意：TestDB 中有很多科目厌蔽，都有1~12月份的發(fā)生額。
AccID ：科目代碼摔癣，Occmonth ：發(fā)生額月份奴饮，DebitOccur ：發(fā)生額。
數(shù)據(jù)庫名：JcyAudit 择浊，數(shù)據(jù)集：Select * from TestDB

select a.*
from TestDB a, 
    (select Occmonth, max(DebitOccur) as Debit101ccur 
    from TestDB 
    where AccID='101' 
    group by Occmonth) b
where a.Occmonth = b.Occmonth and a.DebitOccur > b.Debit101ccur

5. 怎么把這樣一個數(shù)據(jù)表
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成這樣一個結(jié)果戴卜？
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4

select year, 
    (select amount from table m where month=1 and m.year=table.year) as m1,
    (select amount from table m where month=2 and m.year=table.year) as m2,
    (select amount from table m where month=3 and m.year=table.year) as m3,
    (select amount from table m where month=4 and m.year=table.year) as m4
from table group by year

6. 有表A，結(jié)構(gòu)如下：
p_ID p_Num s_id
1 10 01
1 12 02
2 8 01
3 11 01
3 8 03
其中：p_ID為產(chǎn)品ID琢岩，p_Num為產(chǎn)品庫存量投剥，s_id為倉庫ID。
請用SQL語句實現(xiàn)將上表中的數(shù)據(jù)合并担孔，合并后的數(shù)據(jù)為：
p_ID s1_id s2_id s3_id
1 10 12 0
2 8 0 0
3 11 0 8
其中：s1_id為倉庫1的庫存量江锨，s2_id為倉庫2的庫存量，s3_id為倉庫3的庫存量糕篇。如果該產(chǎn)品在某倉庫中無庫存量啄育，那么就是0代替。

select p_id,
    sum(case when s_id=1 then p_num else 0 end) as s1_id,
    sum(case when s_id=2 then p_num else 0 end) as s2_id,
    sum(case when s_id=3 then p_num else 0 end) as s3_id
from myPro group by p_id

下面進入正題拌消。首先創(chuàng)建數(shù)據(jù)表：

學(xué)生表 Student

create table Student(Sid varchar(6), Sname varchar(10), Sage datetime, Ssex varchar(10));
insert into Student values('01' , '趙雷' , '1990-01-01' , '男');
insert into Student values('02' , '錢電' , '1990-12-21' , '男');
insert into Student values('03' , '孫風(fēng)' , '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' , '女')

成績表 SC

create table SC(Sid varchar(10), Cid varchar(10), score decimal(18,1));
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)

課程表 Course

create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));
insert into Course values('01' , '語文' , '02');
insert into Course values('02' , '數(shù)學(xué)' , '01');
insert into Course values('03' , '英語' , '03')

教師表 Teacher

create table Teacher(Tid varchar(10),Tname varchar(10));
insert into Teacher values('01' , '張三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五')

四張表之間的關(guān)聯(lián)很簡單：

表格關(guān)聯(lián)

（以下題目的順序和原文相對應(yīng)）

1. 查詢" 01 "課程比" 02 "課程成績高的學(xué)生的信息及課程分?jǐn)?shù)

select s.*, a.score as score_01, b.score as score_02
from student s,
     (select sid, score from sc where cid=01) a,
     (select sid, score from sc where cid=02) b
where a.sid = b.sid and a.score > b.score and s.sid = a.sid

+------+--------+---------------------+------+----------+----------+
| Sid  | Sname  | Sage                | Ssex | score_01 | score_02 |
+------+--------+---------------------+------+----------+----------+
| 02   | 錢電   | 1990-12-21 00:00:00 | 男   |     70.0 |     60.0 |
| 04   | 李云   | 1990-08-06 00:00:00 | 男   |     50.0 |     30.0 |
+------+--------+---------------------+------+----------+----------+
2 rows in set (0.00 sec)

2. 查詢平均成績大于等于 60 分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績

select s.sid, sname, avg(score) as avg_score
from student as s, sc
where s.sid = sc.sid
group by s.sid
having avg_score > 60

+------+--------+-----------+
| sid  | sname  | avg_score |
+------+--------+-----------+
| 01   | 趙雷   |  89.66667 |
| 02   | 錢電   |  70.00000 |
| 03   | 孫風(fēng)   |  80.00000 |
| 05   | 周梅   |  81.50000 |
| 07   | 鄭竹   |  93.50000 |
+------+--------+-----------+
5 rows in set (0.00 sec)

3. 查詢在 SC 表存在成績的學(xué)生信息

select * from student where sid in (select sid from sc where score is not null)

+------+--------+---------------------+------+
| Sid  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 01   | 趙雷   | 1990-01-01 00:00:00 | 男   |
| 02   | 錢電   | 1990-12-21 00:00:00 | 男   |
| 03   | 孫風(fēng)   | 1990-05-20 00:00:00 | 男   |
| 04   | 李云   | 1990-08-06 00:00:00 | 男   |
| 05   | 周梅   | 1991-12-01 00:00:00 | 女   |
| 06   | 吳蘭   | 1992-03-01 00:00:00 | 女   |
| 07   | 鄭竹   | 1989-07-01 00:00:00 | 女   |
+------+--------+---------------------+------+
7 rows in set (0.00 sec)

4. 查詢所有同學(xué)的學(xué)生編號挑豌、學(xué)生姓名、選課總數(shù)、所有課程的總成績(沒成績的顯示為 null )

這道題得用到left join或者right join氓英，不能用where連接侯勉，因為題目說了要求有顯示為null的，where是inner join债蓝，不會出現(xiàn)null壳鹤，在這道題里會查不出第08號學(xué)生。

select s.sid, s.sname, count(cid) as 選課總數(shù), sum(score) as 總成績
from student as s left join sc
on s.sid = sc.sid
group by s.sid

+------+--------+--------------+-----------+
| sid  | sname  | 選課總數(shù)     | 總成績    |
+------+--------+--------------+-----------+
| 01   | 趙雷   |            3 |     269.0 |
| 02   | 錢電   |            3 |     210.0 |
| 03   | 孫風(fēng)   |            3 |     240.0 |
| 04   | 李云   |            3 |     100.0 |
| 05   | 周梅   |            2 |     163.0 |
| 06   | 吳蘭   |            2 |      65.0 |
| 07   | 鄭竹   |            2 |     187.0 |
| 08   | 王菊   |            0 |      NULL |
+------+--------+--------------+-----------+
8 rows in set (0.00 sec)

4.1 查有成績的學(xué)生信息

select s.sid, s.sname, count(*) as 選課總數(shù), sum(score) as 總成績,
    sum(case when cid = 01 then score else null end) as score_01,
    sum(case when cid = 02 then score else null end) as score_02,
    sum(case when cid = 03 then score else null end) as score_03
from student as s, sc
where s.sid = sc.sid
group by s.sid

+------+--------+--------------+-----------+----------+----------+----------+
| sid  | sname  | 選課總數(shù)     | 總成績    | score_01 | score_02 | score_03 |
+------+--------+--------------+-----------+----------+----------+----------+
| 01   | 趙雷   |            3 |     269.0 |     80.0 |     90.0 |     99.0 |
| 02   | 錢電   |            3 |     210.0 |     70.0 |     60.0 |     80.0 |
| 03   | 孫風(fēng)   |            3 |     240.0 |     80.0 |     80.0 |     80.0 |
| 04   | 李云   |            3 |     100.0 |     50.0 |     30.0 |     20.0 |
| 05   | 周梅   |            2 |     163.0 |     76.0 |     87.0 |     NULL |
| 06   | 吳蘭   |            2 |      65.0 |     31.0 |     NULL |     34.0 |
| 07   | 鄭竹   |            2 |     187.0 |     NULL |     89.0 |     98.0 |
+------+--------+--------------+-----------+----------+----------+----------+
7 rows in set (0.00 sec)

5. 查詢「李」姓老師的數(shù)量

select count(tname) from teacher where tname like '李%'

+--------------+
| count(tname) |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

6. 查詢學(xué)過「張三」老師授課的同學(xué)的信息

select * from student where sid in (
    select sid from sc, course, teacher
    where sc.cid = course.cid
     and course.tid = teacher.tid
     and tname = '張三'
)

+------+--------+---------------------+------+
| Sid  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 01   | 趙雷   | 1990-01-01 00:00:00 | 男   |
| 02   | 錢電   | 1990-12-21 00:00:00 | 男   |
| 03   | 孫風(fēng)   | 1990-05-20 00:00:00 | 男   |
| 04   | 李云   | 1990-08-06 00:00:00 | 男   |
| 05   | 周梅   | 1991-12-01 00:00:00 | 女   |
| 07   | 鄭竹   | 1989-07-01 00:00:00 | 女   |
+------+--------+---------------------+------+
6 rows in set (0.00 sec)

原作者的寫法里面用到了等號 =饰迹，雖然得到同樣的結(jié)果芳誓，但是這樣寫不太好，因為不確定張三老師是不是只教授一門課（只不過現(xiàn)在的數(shù)據(jù)量太小了而已）啊鸭，in 適用于一個或多個返回結(jié)果的情況锹淌，適應(yīng)性比等號更廣。

select * from Student
where sid in(select distinct Sid from SC
where cid=(select Cid from Course
where Tid=(select Tid from Teacher where Tname='張三')))

7. 查詢沒有學(xué)全所有課程的同學(xué)的信息

select * from student where sid in (select sid from sc group by sid having count(cid) < 3)

+------+--------+---------------------+------+
| Sid  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 05   | 周梅   | 1991-12-01 00:00:00 | 女   |
| 06   | 吳蘭   | 1992-03-01 00:00:00 | 女   |
| 07   | 鄭竹   | 1989-07-01 00:00:00 | 女   |
+------+--------+---------------------+------+
3 rows in set (0.00 sec)

9. 查詢和" 01 "號的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息
這道題號稱是所有題目里最難的一道赠制，我雖然做了出來赂摆，但是寫法很麻煩，不必要钟些。原作者寫的很簡潔：

select * from Student
where Sid in(
    select Sid from SC
    where Cid in (select Cid from SC where Sid = '01') and Sid <>'01'
    group by Sid
    having COUNT(Cid)>=3
)

+------+--------+---------------------+------+
| Sid  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 02   | 錢電   | 1990-12-21 00:00:00 | 男   |
| 03   | 孫風(fēng)   | 1990-05-20 00:00:00 | 男   |
| 04   | 李云   | 1990-08-06 00:00:00 | 男   |
+------+--------+---------------------+------+
3 rows in set (0.00 sec)

我寫的就太麻煩啦烟号。。

select * from student where sid in (
    select B.sid
    from
        (select sid,
            sum(case when cid=01 then 1 else 0 end) as course_01,
            sum(case when cid=02 then 1 else 0 end) as course_02,
            sum(case when cid=03 then 1 else 0 end) as course_03
        from sc where sid = 01 group by sid) as A,
        (select sid,
            sum(case when cid=01 then 1 else 0 end) as course_01,
            sum(case when cid=02 then 1 else 0 end) as course_02,
            sum(case when cid=03 then 1 else 0 end) as course_03
        from sc where sid != 01 group by sid) as B
    where A.course_01=B.course_01 and A.course_02=B.course_02 and A.course_03=B.course_03
)

+------+--------+---------------------+------+
| Sid  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 02   | 錢電   | 1990-12-21 00:00:00 | 男   |
| 03   | 孫風(fēng)   | 1990-05-20 00:00:00 | 男   |
| 04   | 李云   | 1990-08-06 00:00:00 | 男   |
+------+--------+---------------------+------+
3 rows in set (0.00 sec)

8. 查詢至少有一門課與學(xué)號為" 01 "的同學(xué)所學(xué)相同的同學(xué)的信息

和第9題基本一致政恍，還是原作者寫的好一些

select * from Student where Sid in(
    select distinct Sid from SC where Cid in(
        select Cid from SC where Sid='01'
    )
)

+------+--------+---------------------+------+
| Sid  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 01   | 趙雷   | 1990-01-01 00:00:00 | 男   |
| 02   | 錢電   | 1990-12-21 00:00:00 | 男   |
| 03   | 孫風(fēng)   | 1990-05-20 00:00:00 | 男   |
| 04   | 李云   | 1990-08-06 00:00:00 | 男   |
| 05   | 周梅   | 1991-12-01 00:00:00 | 女   |
| 06   | 吳蘭   | 1992-03-01 00:00:00 | 女   |
| 07   | 鄭竹   | 1989-07-01 00:00:00 | 女   |
+------+--------+---------------------+------+
7 rows in set (0.00 sec)

10. 查詢沒學(xué)過"張三"老師講授的任一門課程的學(xué)生姓名

一般涉及到"任意"的都會用到not in這樣的取反的結(jié)構(gòu)：

select sname from student
where sname not in (
    select s.sname
    from student as s, course as c, teacher as t, sc
    where s.sid = sc.sid
        and sc.cid = c.cid
        and c.tid = t.tid
        and t.tname = '張三'
)

+--------+
| sname  |
+--------+
| 吳蘭   |
| 王菊   |
+--------+
2 rows in set (0.00 sec)

11. 查詢兩門及其以上不及格課程的同學(xué)的學(xué)號汪拥，姓名及其平均成績

select s.sid, s.sname, avg(score)
from student as s, sc
where s.sid = sc.sid and score<60
group by s.sid
having count(score)>=2

+------+--------+------------+
| sid  | sname  | avg(score) |
+------+--------+------------+
| 04   | 李云   |   33.33333 |
| 06   | 吳蘭   |   32.50000 |
+------+--------+------------+
2 rows in set (0.00 sec)

12. 檢索" 01 "課程分?jǐn)?shù)小于 60，按分?jǐn)?shù)降序排列的學(xué)生信息

select s.* ,score
from student as s, sc
where cid = 01
  and score < 60
  and s.sid=sc.sid
order by score desc

+------+--------+---------------------+------+-------+
| Sid  | Sname  | Sage                | Ssex | score |
+------+--------+---------------------+------+-------+
| 04   | 李云   | 1990-08-06 00:00:00 | 男   |  50.0 |
| 06   | 吳蘭   | 1992-03-01 00:00:00 | 女   |  31.0 |
+------+--------+---------------------+------+-------+
2 rows in set (0.00 sec)

13. 按平均成績從高到低顯示所有學(xué)生的所有課程的成績以及平均成績

select sid,
    sum(case when cid=01 then score else null end) as score_01,
    sum(case when cid=02 then score else null end) as score_02,
    sum(case when cid=03 then score else null end) as score_03,
    avg(score)
from sc group by sid
order by avg(score) desc

+------+----------+----------+----------+------------+
| sid  | score_01 | score_02 | score_03 | avg(score) |
+------+----------+----------+----------+------------+
| 07   |     NULL |     89.0 |     98.0 |   93.50000 |
| 01   |     80.0 |     90.0 |     99.0 |   89.66667 |
| 05   |     76.0 |     87.0 |     NULL |   81.50000 |
| 03   |     80.0 |     80.0 |     80.0 |   80.00000 |
| 02   |     70.0 |     60.0 |     80.0 |   70.00000 |
| 04   |     50.0 |     30.0 |     20.0 |   33.33333 |
| 06   |     31.0 |     NULL |     34.0 |   32.50000 |
+------+----------+----------+----------+------------+
7 rows in set (0.00 sec)

14. 查詢各科成績最高分篙耗、最低分和平均分迫筑，以如下形式顯示：課程 ID，課程 name宗弯，最高分脯燃，最低分，平均分蒙保，及格率辕棚，中等率，優(yōu)良率邓厕，優(yōu)秀率(及格為>=60逝嚎，中等為：70-80，優(yōu)良為：80-90邑狸，優(yōu)秀為：>=90）懈糯。
要求輸出課程號和選修人數(shù)涤妒，查詢結(jié)果按人數(shù)降序排列单雾，若人數(shù)相同，按課程號升序排列

這道題熟練掌握case和sum的用法就沒什么問題

select c.cid as 課程號, c.cname as 課程名稱, count(*) as 選修人數(shù),
    max(score) as 最高分, min(score) as 最低分, avg(score) as 平均分,
    sum(case when score >= 60 then 1 else 0 end)/count(*) as 及格率,
    sum(case when score >= 70 and score < 80 then 1 else 0 end)/count(*) as 中等率,
    sum(case when score >= 80 and score < 90 then 1 else 0 end)/count(*) as 優(yōu)良率,
    sum(case when score >= 90 then 1 else 0 end)/count(*) as 優(yōu)秀率
from sc, course c
where c.cid = sc.cid
group by c.cid
order by count(*) desc, c.cid asc

+-----------+--------------+--------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 課程號    | 課程名稱      | 選修人數(shù)      | 最高分     | 最低分    | 平均分     | 及格率    | 中等率    | 優(yōu)良率     | 優(yōu)秀率     |
+-----------+--------------+--------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 01        | 語文         |            6 |      80.0 |      31.0 |  64.50000 |    0.6667 |    0.3333 |    0.3333 |    0.0000 |
| 02        | 數(shù)學(xué)         |            6 |      90.0 |      30.0 |  72.66667 |    0.8333 |    0.0000 |    0.5000 |    0.1667 |
| 03        | 英語         |            6 |      99.0 |      20.0 |  68.50000 |    0.6667 |    0.0000 |    0.3333 |    0.3333 |
+-----------+--------------+--------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
3 rows in set (0.00 sec)

原作者的寫法本質(zhì)上和我是相同的，但是用了很多l(xiāng)eft join看起來有些冗余

select distinct A.Cid,Cname,最高分,最低分,平均分,及格率,中等率,優(yōu)良率,優(yōu)秀率 from SC A
left join Course on A.Cid=Course.Cid
left join (select Cid,MAX(score)最高分,MIN(score)最低分,AVG(score)平均分 from SC group by Cid)B on A.Cid=B.Cid
left join (select Cid,(convert(decimal(5,2),(sum(case when score>=60 then 1 else 0 end)*1.00)/COUNT(*))*100)及格率 from SC group by Cid)C on A.Cid=C.Cid
left join (select Cid,(convert(decimal(5,2),(sum(case when score >=70 and score<80 then 1 else 0 end)*1.00)/COUNT(*))*100)中等率 from SC group by Cid)D on A.Cid=D.Cid
left join (select Cid,(convert(decimal(5,2),(sum(case when score >=80 and score<90 then 1 else 0 end)*1.00)/COUNT(*))*100)優(yōu)良率 from SC group by Cid)E on A.Cid=E.Cid
left join (select Cid,(convert(decimal(5,2),(sum(case when score >=90 then 1 else 0 end)*1.00)/COUNT(*))*100)優(yōu)秀率
from SC group by Cid)F on A.Cid=F.Cid

15. 按平均成績進行排序硅堆，顯示總排名和各科排名屿储，Score 重復(fù)時保留名次空缺

原題目是按各科成績進行排序，并顯示排名渐逃， Score 重復(fù)時保留名次空缺够掠。但是我沒看明白什么意思，各科成績?nèi)绾闻判蚯丫眨空Z文分?jǐn)?shù)和數(shù)學(xué)分?jǐn)?shù)有可比性嗎疯潭？作者的寫法是select *,RANK()over(order by score desc)排名 from SC，把所有的成績都放到一塊兒排序了面殖，這沒有意義竖哩，不可比。于是我修改了一下題目脊僚。

select s.*, rank_01, rank_02, rank_03, rank_total
from student s
left join (select sid, rank() over(partition by cid order by score desc) as rank_01 from sc where cid=01) A on s.sid=A.sid
left join (select sid, rank() over(partition by cid order by score desc) as rank_02 from sc where cid=02) B on s.sid=B.sid
left join (select sid, rank() over(partition by cid order by score desc) as rank_03 from sc where cid=03) C on s.sid=C.sid
left join (select sid, rank() over(order by avg(score) desc) as rank_total from sc group by sid) D on s.sid=D.sid
order by rank_total asc

+------+--------+---------------------+------+---------+---------+---------+------------+
| Sid  | Sname  | Sage                | Ssex | rank_01 | rank_02 | rank_03 | rank_total |
+------+--------+---------------------+------+---------+---------+---------+------------+
| 08   | 王菊   | 1990-01-20 00:00:00 | 女   |    NULL |    NULL |    NULL |       NULL |
| 07   | 鄭竹   | 1989-07-01 00:00:00 | 女   |    NULL |       2 |       2 |          1 |
| 01   | 趙雷   | 1990-01-01 00:00:00 | 男   |       1 |       1 |       1 |          2 |
| 05   | 周梅   | 1991-12-01 00:00:00 | 女   |       3 |       3 |    NULL |          3 |
| 03   | 孫風(fēng)   | 1990-05-20 00:00:00 | 男   |       1 |       4 |       3 |          4 |
| 02   | 錢電   | 1990-12-21 00:00:00 | 男   |       4 |       5 |       3 |          5 |
| 04   | 李云   | 1990-08-06 00:00:00 | 男   |       5 |       6 |       6 |          6 |
| 06   | 吳蘭   | 1992-03-01 00:00:00 | 女   |       6 |    NULL |       5 |          7 |
+------+--------+---------------------+------+---------+---------+---------+------------+
8 rows in set (0.00 sec)

15.1 按平均成績進行排序相叁，顯示總排名和各科排名，Score 重復(fù)時合并名次

同樣修改了一下題目辽幌。15題和15.1題的指向很明確了增淹，就是rank()和dense_rank()的區(qū)別，也就是兩個并列第一名之后的那個人是第三名(rank)還是第二名(dense_rank)的區(qū)別乌企。

select s.*, rank_01, rank_02, rank_03, rank_total
from student s
left join (select sid, dense_rank() over(partition by cid order by score desc) as rank_01 from sc where cid=01) A on s.sid=A.sid
left join (select sid, dense_rank() over(partition by cid order by score desc) as rank_02 from sc where cid=02) B on s.sid=B.sid
left join (select sid, dense_rank() over(partition by cid order by score desc) as rank_03 from sc where cid=03) C on s.sid=C.sid
left join (select sid, dense_rank() over(order by avg(score) desc) as rank_total from sc group by sid) D on s.sid=D.sid
order by rank_total asc

+------+--------+---------------------+------+---------+---------+---------+------------+
| Sid  | Sname  | Sage                | Ssex | rank_01 | rank_02 | rank_03 | rank_total |
+------+--------+---------------------+------+---------+---------+---------+------------+
| 08   | 王菊   | 1990-01-20 00:00:00 | 女   |    NULL |    NULL |    NULL |       NULL |
| 07   | 鄭竹   | 1989-07-01 00:00:00 | 女   |    NULL |       2 |       2 |          1 |
| 01   | 趙雷   | 1990-01-01 00:00:00 | 男   |       1 |       1 |       1 |          2 |
| 05   | 周梅   | 1991-12-01 00:00:00 | 女   |       2 |       3 |    NULL |          3 |
| 03   | 孫風(fēng)   | 1990-05-20 00:00:00 | 男   |       1 |       4 |       3 |          4 |
| 02   | 錢電   | 1990-12-21 00:00:00 | 男   |       3 |       5 |       3 |          5 |
| 04   | 李云   | 1990-08-06 00:00:00 | 男   |       4 |       6 |       5 |          6 |
| 06   | 吳蘭   | 1992-03-01 00:00:00 | 女   |       5 |    NULL |       4 |          7 |
+------+--------+---------------------+------+---------+---------+---------+------------+
8 rows in set (0.00 sec)

17. 統(tǒng)計各科成績各分?jǐn)?shù)段人數(shù)：課程編號虑润，課程名稱，[100-85]逛犹，[85-70]端辱，[70-60]，[60-0] 及所占百分比

select c.cid as 課程編號, c.cname as 課程名稱, A.*
from course as c,
(select cid,
    sum(case when score >= 85 then 1 else 0 end)/count(*) as 100_85,
    sum(case when score >= 70 and score < 85 then 1 else 0 end)/count(*) as 85_70,
    sum(case when score >= 60 and score < 70 then 1 else 0 end)/count(*) as 70_60,
    sum(case when score < 60 then 1 else 0 end)/count(*) as 60_0
from sc group by cid) as A
where c.cid = A.cid

+--------------+--------------+------+--------+--------+--------+--------+
| 課程編號     | 課程名稱      | cid  | 100_85 | 85_70  | 70_60  | 60_0   |
+--------------+--------------+------+--------+--------+--------+--------+
| 01           | 語文         | 01   | 0.0000 | 0.6667 | 0.0000 | 0.3333 |
| 02           | 數(shù)學(xué)         | 02   | 0.5000 | 0.1667 | 0.1667 | 0.1667 |
| 03           | 英語         | 03   | 0.3333 | 0.3333 | 0.0000 | 0.3333 |
+--------------+--------------+------+--------+--------+--------+--------+
3 rows in set (0.00 sec)

18. 查詢各科成績前三名的記錄

這是我比較喜歡的一道題目虽画，非常經(jīng)典舞蔽。

select * from (select *, rank() over(partition by cid order by score desc) as graderank from sc) A 
where A.graderank <= 3

| Sid  | Cid  | score | graderank |
+------+------+-------+-----------+
| 01   | 01   |  80.0 |         1 |
| 03   | 01   |  80.0 |         1 |
| 05   | 01   |  76.0 |         3 |
| 01   | 02   |  90.0 |         1 |
| 07   | 02   |  89.0 |         2 |
| 05   | 02   |  87.0 |         3 |
| 01   | 03   |  99.0 |         1 |
| 07   | 03   |  98.0 |         2 |
| 02   | 03   |  80.0 |         3 |
| 03   | 03   |  80.0 |         3 |
+------+------+-------+-----------+
10 rows in set (0.00 sec)

20. 查詢出只選修兩門課程的學(xué)生學(xué)號和姓名

select s.sid, s.sname, count(cid)
from student s, sc
where s.sid = sc.sid
group by s.sid
having count(cid)=2

+------+--------+------------+
| sid  | sname  | count(cid) |
+------+--------+------------+
| 05   | 周梅   |          2 |
| 06   | 吳蘭   |          2 |
| 07   | 鄭竹   |          2 |
+------+--------+------------+
3 rows in set (0.00 sec)

22. 查詢名字中含有「風(fēng)」字的學(xué)生信息

select * from student where sname like '%風(fēng)%'

+------+--------+---------------------+------+
| Sid  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 03   | 孫風(fēng)   | 1990-05-20 00:00:00 | 男   |
+------+--------+---------------------+------+
1 row in set (0.00 sec)

24. 查詢 1990 年出生的學(xué)生名單

select * from student where year(sage) = 1990

+------+--------+---------------------+------+
| Sid  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 01   | 趙雷   | 1990-01-01 00:00:00 | 男   |
| 02   | 錢電   | 1990-12-21 00:00:00 | 男   |
| 03   | 孫風(fēng)   | 1990-05-20 00:00:00 | 男   |
| 04   | 李云   | 1990-08-06 00:00:00 | 男   |
| 08   | 王菊   | 1990-01-20 00:00:00 | 女   |
+------+--------+---------------------+------+
5 rows in set (0.00 sec)

33. 成績不重復(fù)，查詢選修「張三」老師所授課程的學(xué)生中码撰，成績最高的學(xué)生信息及其成績

select s.*, max(score)
from student s, teacher t, course c, sc
where s.sid = sc.sid
    and sc.cid = c.cid
    and c.tid = t.tid
    and t.tname = '張三'

+------+--------+---------------------+------+------------+
| Sid  | Sname  | Sage                | Ssex | max(score) |
+------+--------+---------------------+------+------------+
| 01   | 趙雷   | 1990-01-01 00:00:00 | 男   |       90.0 |
+------+--------+---------------------+------+------------+
1 row in set (0.00 sec)

34. 成績有重復(fù)的情況下渗柿，查詢選修「張三」老師所授課程的學(xué)生中，成績最高的學(xué)生信息及其成績

select * from (
    select *, DENSE_RANK() over (order by score desc) A
    from SC
    where Cid = (select Cid from Course where Tid=(select Tid from Teacher where Tname='張三'))
) B
where B.A=1

+------+------+-------+---+
| Sid  | Cid  | score | A |
+------+------+-------+---+
| 01   | 02   |  90.0 | 1 |
+------+------+-------+---+
1 row in set (0.00 sec)

40. 查詢各學(xué)生的年齡脖岛，只按年份來算

select sname, year(now())-year(sage) as age from student

+--------+------+
| sname  | age  |
+--------+------+
| 趙雷   |   28 |
| 錢電   |   28 |
| 孫風(fēng)   |   28 |
| 李云   |   28 |
| 周梅   |   27 |
| 吳蘭   |   26 |
| 鄭竹   |   29 |
| 王菊   |   28 |
+--------+------+
8 rows in set (0.00 sec)

41. 按照出生日期來算朵栖，當(dāng)前月日 < 出生年月的月日則，年齡減一

select sname, timestampdiff(year, sage, now()) as age from student

+--------+------+
| sname  | age  |
+--------+------+
| 趙雷   |   28 |
| 錢電   |   27 |
| 孫風(fēng)   |   28 |
| 李云   |   27 |
| 周梅   |   26 |
| 吳蘭   |   26 |
| 鄭竹   |   29 |
| 王菊   |   28 |
+--------+------+
8 rows in set (0.00 sec)

42. 查詢本周過生日的學(xué)生

select * from student where week(now()) = week(sage)

Empty set (0.00 sec)

43. 查詢下周過生日的學(xué)生

select * from student where (week(now())+1) = week(sage)

+------+--------+---------------------+------+
| Sid  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 04   | 李云   | 1990-08-06 00:00:00 | 男   |
+------+--------+---------------------+------+
1 row in set (0.00 sec)

44. 查詢本月過生日的學(xué)生

select * from student where month(now()) = month(sage)

+------+--------+---------------------+------+
| Sid  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 07   | 鄭竹   | 1989-07-01 00:00:00 | 女   |
+------+--------+---------------------+------+
1 row in set (0.00 sec)

45. 查詢下月過生日的學(xué)生

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

+------+--------+---------------------+------+
| Sid  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 04   | 李云   | 1990-08-06 00:00:00 | 男   |
+------+--------+---------------------+------+
1 row in set (0.00 sec)

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末柴梆，一起剝皮案震驚了整個濱河市陨溅，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌绍在，老刑警劉巖门扇，帶你破解...
沈念sama閱讀 218,755評論 6贊 507
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件雹有，死亡現(xiàn)場離奇詭異，居然都是意外死亡臼寄，警方通過查閱死者的電腦和手機霸奕，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 93,305評論 3贊 395
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來吉拳，“玉大人质帅，你說我怎么就攤上這事×粼埽” “怎么了煤惩？”我有些...
開封第一講書人閱讀 165,138評論 0贊 355
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵，是天一觀的道長炼邀。經(jīng)常有香客問我盟庞，道長，這世上最難降的妖魔是什么汤善？我笑而不...
開封第一講書人閱讀 58,791評論 1贊 295
?港島之戀（遺憾婚禮）
正文為了忘掉前任什猖，我火速辦了婚禮，結(jié)果婚禮上红淡，老公的妹妹穿的比我還像新娘不狮。我一直安慰自己，他們只是感情好在旱，可當(dāng)我...
茶點故事閱讀 67,794評論 6贊 392
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布摇零。她就那樣靜靜地躺著，像睡著了一般桶蝎。火紅的嫁衣襯著肌膚如雪驻仅。梳的紋絲不亂的頭發(fā)上，一...
開封第一講書人閱讀 51,631評論 1贊 305
城市分裂傳說
那天登渣，我揣著相機與錄音噪服，去河邊找鬼。笑死，一個胖子當(dāng)著我的面吹牛，可吹牛的內(nèi)容都是我干的卵佛。我是一名探鬼主播，決...
沈念sama閱讀 40,362評論 3贊 418
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼雹顺，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了廊遍？” 一聲冷哼從身側(cè)響起嬉愧，我...
開封第一講書人閱讀 39,264評論 0贊 276
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎喉前，沒想到半個月后没酣，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體揽惹，經(jīng)...
沈念sama閱讀 45,724評論 1贊 315
?護林員之死
正文獨居荒郊野嶺守林人離奇死亡，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點故事閱讀 37,900評論 3贊 336
?白月光啟示錄
正文我和宋清朗相戀三年四康，在試婚紗的時候發(fā)現(xiàn)自己被綠了。大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片狭握。...
茶點故事閱讀 40,040評論 1贊 350
活死人
序言：一個原本活蹦亂跳的男人離奇死亡闪金，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出论颅，到底是詐尸還是另有隱情哎垦，我是刑警寧澤，帶...
沈念sama閱讀 35,742評論 5贊 346
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布恃疯，位于F島的核電站漏设，受9級特大地震影響，放射性物質(zhì)發(fā)生泄漏今妄。R本人自食惡果不足惜郑口，卻給世界環(huán)境...
茶點故事閱讀 41,364評論 3贊 330
男人毒藥：我在死后第九天來索命
文/蒙蒙一、第九天我趴在偏房一處隱蔽的房頂上張望盾鳞。院中可真熱鬧犬性，春花似錦、人聲如沸腾仅。這莊子的主人今日做“春日...
開封第一講書人閱讀 31,944評論 0贊 22
一樁弒父案，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽推励。三九已至鹤耍，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間验辞，已是汗流浹背稿黄。一陣腳步聲響...
開封第一講書人閱讀 33,060評論 1贊 270
情欲美人皮
我被黑心中介騙來泰國打工，沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留跌造，地道東北人抛猖。一個月前我還...
沈念sama閱讀 48,247評論 3贊 371
代替公主和親
正文我出身青樓，卻偏偏與公主長得像鼻听，于是被迫代替她去往敵國和親财著。傳聞我的和親對象是個殘疾皇子，可洞房花燭夜當(dāng)晚...
茶點故事閱讀 44,979評論 2贊 355

經(jīng)典SQL練習(xí)題(MySQL版)

推薦閱讀更多精彩內(nèi)容