常見的 SQL 面試題：經典 50 例

_ 作者：_sh_c_2450957609

blog.csdn.net/u010565545/article/details/100785261

SQL 基礎知識整理

• select 查詢結果，如: [學號,平均成績：組函數avg(成績)]

• from 從哪張表中查找數據，如:[涉及到成績：成績表score]

• where 查詢條件，如:[b.課程號='0003' and b.成績>80]

• group by 分組，如:[每個學生的平均：按學號分組](oracle,SQL server 中出現在 select 子句後的非分組函數，必須出現在 group by 子句後出現),MySQL 中可以不用

• having 對分組結果指定條件，如:[大於60分]

• order by 對查詢結果排序，如:[增序: 成績  ASC / 降序: 成績 DESC];

• limit 使用 limt 子句返回 topN（對應這個問題返回的成績前兩名），如:[ limit  2 ==>從0索引開始讀取2個]limit==> 從 0 索引開始 [0,N-1]

select * from table limit 2,1;                
 
-- 含義是跳過2條取出1條數據，limit後面是從第2條開始讀，讀取1條信息，即讀取第3條數據
 
select * from table limit 2 offset 1;     
 
-- 含義是從第1條（不包括）數據開始取出2條數據，limit後面跟的是2條數據，offset後面是從第1條開始讀取，即讀取第2,3條

組函數: 去重 distinct()  統計總數 sum()   計算個數 count()  平均數 avg()  最大值 max() 最小數 min()

多表連接: 內連接 (省略默認 inner) join ...on.. 左連接 left join tableName as b on a.key ==b.key 右連接 right join  連接 union(無重複(過濾去重)) 和 union all(有重複[不過濾去重])

• union 並集

• union all(有重複)

oracle(SQL server) 數據庫

• intersect 交集

• minus(except) 相減 (差集)

oracle

一、數據庫對象：表 (table)  視圖 (view)  序列 (sequence)  索引 (index)  同義詞 (synonym)

1. 視圖: 存儲起來的 select 語句

create view emp_vw
as
select employee_id, last_name, salary
from employees
where department_id = 90;

select * from emp_vw;

可以對簡單視圖進行 DML 操作

update emp_vw
set last_name = 'HelloKitty'
where employee_id = 100;

select * from employees
where employee_id = 100;

1). 複雜視圖

create view emp_vw2
as
select department_id, avg(salary) avg_sal
from employees
group by department_id;

select * from emp_vw2;

複雜視圖不能進行 DML 操作

update emp_vw2
set avg_sal = 10000
where department_id = 100;

2. 序列：用於生成一組有規律的數值。（通常用於爲主鍵設置值）

create sequence emp_seq1
start with 1
increment by 1
maxvalue 10000
minvalue 1
cycle
nocache;

select emp_seq1.currval from dual;

select emp_seq1.nextval from dual;

問題：裂縫，原因：

• 當多個表共用同一個序列時。

• rollback

• 發生異常

create table emp1(
       id number(10),
       name varchar2(30)
);

insert into emp1
values(emp_seq1.nextval, '張三');

select * from emp1;

3. 索引：提高查詢效率

自動創建：Oracle 會爲具有唯一約束 (唯一約束，主鍵約束) 的列，自動創建索引

create table emp2(
       id number(10) primary key,
       name varchar2(30)
)

手動創建

create index emp_idx
on emp2(name);

create index emp_idx2
on emp2(id, name);

4. 同義詞

create synonym d1 for departments;

select * from d1;

5. 表：

DDL ：數據定義語言 create table .../ drop table ... / rename ... to..../ truncate table.../alter table ...

DML : 數據操縱語言

insert into ... values ...
update ... set ... where ...
delete from ... where ...

【重要】

• select ... 組函數 (MIN()/MAX()/SUM()/AVG()/COUNT())

• from ...join ... on ... 左外連接：left join ... on ... 右外連接: right join ... on ...

• where ...

• group by ... (oracle,SQL server 中出現在 select 子句後的非分組函數，必須出現在 group by 子句後)

• having ... 用於過濾 組函數

• order by ... asc 升序， desc 降序

• limit (0,4) 限制 N 條數據 如: topN 數據

• union 並集

• union all(有重複)

• intersect 交集

• minus 相減

DCL : 數據控制語言  commit : 提交 / rollback : 回滾 / 授權 grant...to...  /revoke

索引

何時創建索引:

一、

select employee_id, last_name, salary, department_id
from employees
where department_id in (70, 80) --> 70:1  80:34

• union 並集

• union all(有重複部分)

• intersect 交集

• minus 相減

select employee_id, last_name, salary, department_id
from employees
where department_id in (80, 90)  --> 90:4  80:34

問題：查詢工資大於 149 號員工工資的員工的信息

select * 
from employees
where salary > (
      select salary
      from employees
      where employee_id = 149
)

問題：查詢與 141 號或 174 號員工的 manager_id 和 department_id 相同的其他員工的 employee_id, manager_id, department_id

select employee_id, manager_id, department_id
from employees
where manager_id in (
      select manager_id
      from employees
      where employee_id in(141, 174)
) and department_id in (
      select department_id
      from employees
      where employee_id in(141, 174)
) and employee_id not in (141, 174);

select employee_id, manager_id, department_id
from employees
where (manager_id, department_id) in (
      select manager_id, department_id
      from employees
      where employee_id in (141, 174)
) and employee_id not in(141, 174);

1. from 子句中使用子查詢

select max(avg(salary))
from employees
group by department_id;

select max(avg_sal)
from (
      select avg(salary) avg_sal
      from employees
      group by department_id
) e

• 問題：返回比本部門平均工資高的員工的 last_name, department_id, salary 及平均工資

select last_name, department_id, salary, (select avg(salary) from employees where department_id = e1.department_id)
from employees e1
where salary > (
      select avg(salary)
      from employees e2
      where e1.department_id = e2.department_id
)

select last_name, e1.department_id, salary, avg_sal
from employees e1, (
     select department_id, avg(salary) avg_sal
     from employees
     group by department_id
) e2
where e1.department_id = e2.department_id
and e1.salary > e2.avg_sal;

case...when ... then... when ... then ... else ... end

• 查詢：若部門爲 10 查看工資的 1.1 倍，部門號爲 20 工資的 1.2 倍，其餘 1.3 倍

SELECT
 employee_id,
 last_name,
 salary,
CASE
  department_id 
  WHEN 10 THEN
  salary * 1.1                                                           
  WHEN 20 THEN
  salary * 1.2  ELSE salary * 1.3                                                           
 END "new_salary" 
FROM
 employees;
SELECT
 employee_id,
 last_name,
 salary,
 decode( department_id, 10, salary * 1.1, 20, salary * 1.2,  salary * 1.3 ) "new_salary" 
FROM
 employees;

• 問題：顯式員工的 employee_id,last_name 和 location。其中，若員工 department_id 與 location_id 爲 1800 的 department_id 相同，則 location 爲’Canada’, 其餘則爲’USA’。

select employee_id, last_name, case department_id when (
                    select department_id
                    from departments
                    where location_id = 1800
) then 'Canada' else 'USA' end "location"
from employees;

• 問題：查詢員工的 employee_id,last_name, 要求按照員工的 department_name 排序

select employee_id, last_name
from employees e1
order by (
      select department_name
      from departments d1
      where e1.department_id = d1.department_id
)

SQL 優化：能使用 EXISTS 就不要使用 IN

• 問題：查詢公司管理者的 employee_id,last_name,job_id,department_id 信息

select employee_id, last_name, job_id, department_id
from employees
where employee_id in (
      select manager_id
      from employees
)


select employee_id, last_name, job_id, department_id
from employees e1
where exists (
      select 'x'
      from employees e2
      where e1.employee_id = e2.manager_id
)

• 問題：查詢 departments 表中，不存在於 employees 表中的部門的 department_id 和 department_name

select department_id, department_name
from departments d1
where not exists (
      select 'x'
      from employees e1
      where e1.department_id = d1.department_id
)

• 更改 108 員工的信息: 使其工資變爲所在部門中的最高工資, job 變爲公司中平均工資最低的 job

update employees e1
set salary = (
    select max(salary)
    from employees e2
    where e1.department_id = e2.department_id
), job_id = (
   select job_id
   from employees
   group by job_id
   having avg(salary) = (
         select min(avg(salary))
         from employees
         group by job_id
   )
)
where employee_id = 108;

• 刪除 108 號員工所在部門中工資最低的那個員工.

delete from employees e1
where salary = (
      select min(salary)
      from employees
      where department_id = (
            select department_id
            from employees
            where employee_id = 108
      )
)

select * from employees where employee_id = 108;
select * from employees where department_id = 100
order by salary;

rollback;

常見的 SQL 面試題：經典 50 題

已知有如下 4 張表：

• 學生表：student(學號, 學生姓名, 出生年月, 性別)

• 成績表：score(學號, 課程號, 成績)

• 課程表：course(課程號, 課程名稱, 教師號)

• 教師表：teacher(教師號, 教師姓名)

根據以上信息按照下面要求寫出對應的 SQL 語句。（搜索公衆號 Java 知音，回覆 “2021”，送你一份 Java 面試題寶典）

ps：這些題考察 SQL 的編寫能力，對於這類型的題目，需要你先把 4 張表之間的關聯關係搞清楚了，最好的辦法是自己在草稿紙上畫出關聯圖，然後再編寫對應的 SQL 語句就比較容易了。下圖是我畫的這 4 張表的關係圖，可以看出它們之間是通過哪些外鍵關聯起來的：

一、創建數據庫和表

爲了演示題目的運行過程，我們先按下面語句在客戶端 navicat 中創建數據庫和表。

如何你還不懂什麼是數據庫，什麼是客戶端 navicat，可以先學習這個：

1. 創建表

1）創建學生表（student）

按下圖在客戶端 navicat 裏創建學生表。推薦：250 期面試題彙總

學生表的 “學號” 列設置爲主鍵約束，下圖是每一列設置的數據類型和約束

創建完表，點擊 “保存”

2）創建成績表（score）

同樣的步驟，創建 " 成績表 “。“課程表的“學號” 和“課程號”一起設置爲主鍵約束（聯合主鍵），“成績”這一列設置爲數值類型（float，浮點數值）

3）創建課程表（course）

課程表的 “課程號” 設置爲主鍵約束

4）教師表（teacher）

教師表的 “教師號” 列設置爲主鍵約束，教師姓名這一列設置約束爲 “null”（紅框的地方不勾選），表示這一列允許包含空值（null）。推薦：250 期面試題彙總

向表中添加數據

1）向學生表裏添加數據

添加數據的 sql

insert into student(學號,姓名,出生日期,性別) 
values('0001' , '猴子' , '1989-01-01' , '男');
 
insert into student(學號,姓名,出生日期,性別) 
values('0002' , '猴子' , '1990-12-21' , '女');
 
insert into student(學號,姓名,出生日期,性別) 
values('0003' , '馬雲' , '1991-12-21' , '男');
 
insert into student(學號,姓名,出生日期,性別) 
values('0004' , '王思聰' , '1990-05-20' , '男');

在客戶端 navicat 裏的操作

2）成績表（score）

添加數據的 sql

insert into score(學號,課程號,成績) 
values('0001' , '0001' , 80);
 
insert into score(學號,課程號,成績) 
values('0001' , '0002' , 90);
 
insert into score(學號,課程號,成績) 
values('0001' , '0003' , 99);
 
insert into score(學號,課程號,成績) 
values('0002' , '0002' , 60);
 
insert into score(學號,課程號,成績) 
values('0002' , '0003' , 80);
 
insert into score(學號,課程號,成績) 
values('0003' , '0001' , 80);
 
insert into score(學號,課程號,成績) 
values('0003' , '0002' , 80);
 
insert into score(學號,課程號,成績) 
values('0003' , '0003' , 80);

客戶端 navicat 裏的操作

3）課程表

添加數據的 sql

insert into course(課程號,課程名稱,教師號)
values('0001' , '語文' , '0002');
 
insert into course(課程號,課程名稱,教師號)
values('0002' , '數學' , '0001');
 
insert into course(課程號,課程名稱,教師號)
values('0003' , '英語' , '0003');

客戶端 navicat 裏的操作

4）教師表裏添加數據

添加數據的 sql

-- 教師表：添加數據
insert into teacher(教師號,教師姓名) 
values('0001' , '孟扎扎');
 
insert into teacher(教師號,教師姓名) 
values('0002' , '馬化騰');
 
-- 這裏的教師姓名是空值（null）
insert into teacher(教師號,教師姓名) 
values('0003' , null);
 
-- 這裏的教師姓名是空字符串（''）
insert into teacher(教師號,教師姓名) 
values('0004' , '');

客戶端 navicat 裏操作

添加結果

三、50 道面試題

爲了方便學習，我將 50 道面試題進行了分類

查詢姓 “猴” 的學生名單

查詢姓 “孟” 老師的個數

select count(教師號)
from teacher
where 教師姓名 like '孟%';

2. 彙總統計分組分析

面試題：查詢課程編號爲 “0002” 的總成績

--分析思路
--select 查詢結果 [總成績:彙總函數sum]
--from 從哪張表中查找數據[成績表score]
--where 查詢條件 [課程號是0002]
select sum(成績)
from score
where 課程號 = '0002';

查詢選了課程的學生人數

--這個題目翻譯成大白話就是：查詢有多少人選了課程
--select 學號，成績表裏學號有重複值需要去掉
--from 從課程表查找score;
select count(distinct 學號) as 學生人數 
from score;

查詢各科成績最高和最低的分， 以如下的形式顯示：課程號，最高分，最低分

/*
分析思路
select 查詢結果 [課程ID：是課程號的別名,最高分：max(成績) ,最低分：min(成績)]
from 從哪張表中查找數據 [成績表score]
where 查詢條件 [沒有]
group by 分組 [各科成績：也就是每門課程的成績，需要按課程號分組];
*/
select 課程號,max(成績) as 最高分,min(成績) as 最低分
from score
group by 課程號;

查詢每門課程被選修的學生數

/*
分析思路
select 查詢結果 [課程號，選修該課程的學生數：彙總函數count]
from 從哪張表中查找數據 [成績表score]
where 查詢條件 [沒有]
group by 分組 [每門課程：按課程號分組];
*/
select 課程號, count(學號)
from score
group by 課程號;

查詢男生、女生人數

/*
分析思路
select 查詢結果 [性別，對應性別的人數：彙總函數count]
from 從哪張表中查找數據 [性別在學生表中，所以查找的是學生表student]
where 查詢條件 [沒有]
group by 分組 [男生、女生人數：按性別分組]
having 對分組結果指定條件 [沒有]
order by 對查詢結果排序[沒有];
*/
select 性別,count(*)
from student
group by 性別;

查詢平均成績大於 60 分學生的學號和平均成績

/* 
題目翻譯成大白話：
平均成績：展開來說就是計算每個學生的平均成績
這裏涉及到“每個”就是要分組了
平均成績大於60分，就是對分組結果指定條件
分析思路
select 查詢結果 [學號，平均成績：彙總函數avg(成績)]
from 從哪張表中查找數據 [成績在成績表中，所以查找的是成績表score]
where 查詢條件 [沒有]
group by 分組 [平均成績：先按學號分組，再計算平均成績]
having 對分組結果指定條件 [平均成績大於60分]
*/
select 學號, avg(成績)
from score
group by 學號
having avg(成績)>60;

查詢至少選修兩門課程的學生學號

/* 
翻譯成大白話：
第1步，需要先計算出每個學生選修的課程數據，需要按學號分組
第2步，至少選修兩門課程：也就是每個學生選修課程數目>=2，對分組結果指定條件
分析思路
select 查詢結果 [學號,每個學生選修課程數目：彙總函數count]
from 從哪張表中查找數據 [課程的學生學號：課程表score]
where 查詢條件 [至少選修兩門課程：需要先計算出每個學生選修了多少門課，需要用分組，所以這裏沒有where子句]
group by 分組 [每個學生選修課程數目：按課程號分組，然後用匯總函數count計算出選修了多少門課]
having 對分組結果指定條件 [至少選修兩門課程：每個學生選修課程數目>=2]
*/
select 學號, count(課程號) as 選修課程數目
from score
group by 學號
having count(課程號)>=2;

查詢同名同性學生名單並統計同名人數

/* 
翻譯成大白話，問題解析：
1）查找出姓名相同的學生有誰，每個姓名相同學生的人數
查詢結果：姓名,人數
條件：怎麼算姓名相同？按姓名分組後人數大於等於2，因爲同名的人數大於等於2
分析思路
select 查詢結果 [姓名,人數：彙總函數count(*)]
from 從哪張表中查找數據 [學生表student]
where 查詢條件 [沒有]
group by 分組 [姓名相同：按姓名分組]
having 對分組結果指定條件 [姓名相同：count(*)>=2]
order by 對查詢結果排序[沒有];
*/
 
select 姓名,count(*) as 人數
from student
group by 姓名
having count(*)>=2;

查詢不及格的課程並按課程號從大到小排列

/* 
分析思路
select 查詢結果 [課程號]
from 從哪張表中查找數據 [成績表score]
where 查詢條件 [不及格：成績 <60]
group by 分組 [沒有]
having 對分組結果指定條件 [沒有]
order by 對查詢結果排序[課程號從大到小排列：降序desc];
*/
select 課程號
from score 
where 成績<60
order by 課程號 desc;

查詢每門課程的平均成績，結果按平均成績升序排序，平均成績相同時，按課程號降序排列

/* 
分析思路
select 查詢結果 [課程號,平均成績：彙總函數avg(成績)]
from 從哪張表中查找數據 [成績表score]
where 查詢條件 [沒有]
group by 分組 [每門課程：按課程號分組]
having 對分組結果指定條件 [沒有]
order by 對查詢結果排序[按平均成績升序排序:asc，平均成績相同時，按課程號降序排列:desc];
*/
select 課程號, avg(成績) as 平均成績
from score
group by 課程號
order by 平均成績 asc,課程號 desc;

檢索課程編號爲 “0004” 且分數小於 60 的學生學號，結果按按分數降序排列

/* 
分析思路
select 查詢結果 []
from 從哪張表中查找數據 [成績表score]
where 查詢條件 [課程編號爲“04”且分數小於60]
group by 分組 [沒有]
having 對分組結果指定條件 []
order by 對查詢結果排序[查詢結果按按分數降序排列];
*/
select 學號
from score
where 課程號='04' and 成績 <60
order by 成績 desc;

統計每門課程的學生選修人數 (超過 2 人的課程才統計)

要求輸出課程號和選修人數，查詢結果按人數降序排序，若人數相同，按課程號升序排序

/* 
分析思路
select 查詢結果 [要求輸出課程號和選修人數]
from 從哪張表中查找數據 []
where 查詢條件 []
group by 分組 [每門課程：按課程號分組]
having 對分組結果指定條件 [學生選修人數(超過2人的課程才統計)：每門課程學生人數>2]
order by 對查詢結果排序[查詢結果按人數降序排序，若人數相同，按課程號升序排序];
*/
select 課程號, count(學號) as '選修人數'
from score
group by 課程號
having count(學號)>2
order by count(學號) desc,課程號 asc;

查詢兩門以上不及格課程的同學的學號及其平均成績

/*
分析思路
先分解題目：
1）[兩門以上][不及格課程]限制條件
2）[同學的學號及其平均成績]，也就是每個學生的平均成績，顯示學號，平均成績
分析過程：
第1步：得到每個學生的平均成績，顯示學號，平均成績
第2步：再加上限制條件：
1）不及格課程
2）兩門以上[不及格課程]：課程數目>2
 
 
/* 
第1步：得到每個學生的平均成績，顯示學號，平均成績
select 查詢結果 [學號,平均成績：彙總函數avg(成績)]
from 從哪張表中查找數據 [涉及到成績：成績表score]
where 查詢條件 [沒有]
group by 分組 [每個學生的平均：按學號分組]
having 對分組結果指定條件 [沒有]
order by 對查詢結果排序[沒有];
*/
select 學號, avg(成績) as 平均成績
from score
group by 學號;
 
 
/* 
第2步：再加上限制條件：
1）不及格課程
2）兩門以上[不及格課程]
select 查詢結果 [學號,平均成績：彙總函數avg(成績)]
from 從哪張表中查找數據 [涉及到成績：成績表score]
where 查詢條件 [限制條件：不及格課程，平均成績<60]
group by 分組 [每個學生的平均：按學號分組]
having 對分組結果指定條件 [限制條件：課程數目>2,彙總函數count(課程號)>2]
order by 對查詢結果排序[沒有];
*/
select 學號, avg(成績) as 平均成績
from score
where 成績 <60
group by 學號
having count(課程號)>=2;

如果上面題目不會做，可以複習這部分涉及到的 sql 知識：

3. 複雜查詢

查詢所有課程成績小於 60 分學生的學號、姓名

【知識點】子查詢

1. 翻譯成大白話

1）查詢結果：學生學號，姓名 2）查詢條件：所有課程成績 < 60 的學生，需要從成績表裏查找，用到子查詢

第 1 步，寫子查詢（所有課程成績 < 60 的學生）

• select 查詢結果 [學號]

• from 從哪張表中查找數據 [成績表：score]

• where 查詢條件 [成績 < 60]

• group by 分組 [沒有]

• having 對分組結果指定條件 [沒有]

• order by 對查詢結果排序 [沒有]

• limit 從查詢結果中取出指定行 [沒有];

select 學號 
from score
where 成績 < 60;

第 2 步，查詢結果：學生學號，姓名，條件是前面 1 步查到的學號

• select 查詢結果 [學號, 姓名]

• from 從哪張表中查找數據 [學生表: student]

• where 查詢條件 [用到運算符 in]

• group by 分組 [沒有]

• having 對分組結果指定條件 [沒有]

• order by 對查詢結果排序 [沒有]

• limit 從查詢結果中取出指定行 [沒有];

select 學號,姓名
from student
where  學號 in (
select 學號 
from score
where 成績 < 60
);

查詢沒有學全所有課的學生的學號、姓名

/*
查找出學號，條件：沒有學全所有課，也就是該學生選修的課程數 < 總的課程數
【考察知識點】in，子查詢
*/
select 學號,姓名
from student
where 學號 in(
select 學號 
from score
group by 學號
having count(課程號) < (select count(課程號) from course)
);

查詢出只選修了兩門課程的全部學生的學號和姓名

select 學號,姓名
from student
where 學號 in(
select 學號
from score
group by 學號
having count(課程號)=2
);

1990 年出生的學生名單

/*
查找1990年出生的學生名單
學生表中出生日期列的類型是datetime
*/
select 學號,姓名 
from student 
where year(出生日期)=1990;

查詢各科成績前兩名的記錄

這類問題其實就是常見的：分組取每組最大值、最小值，每組最大的 N 條（top N）記錄。

sql 面試題：topN 問題

工作中會經常遇到這樣的業務問題：

• 如何找到每個類別下用戶最喜歡的產品是哪個？

• 如果找到每個類別下用戶點擊最多的 5 個商品是什麼？

這類問題其實就是常見的：分組取每組最大值、最小值，每組最大的 N 條（top N）記錄。

面對該類問題，如何解決呢？

下面我們通過成績表的例子來給出答案。

成績表是學生的成績，裏面有學號（學生的學號），課程號（學生選修課程的課程號），成績（學生選修該課程取得的成績）

分組取每組最大值

案例：按課程號分組取成績最大值所在行的數據

我們可以使用分組（group by）和彙總函數得到每個組裏的一個值（最大值，最小值，平均值等）。但是無法得到成績最大值所在行的數據。

select 課程號,max(成績) as 最大成績
from score 
group by 課程號;

我們可以使用關聯子查詢來實現：

select * 
from score as a 
where 成績 = (
select max(成績) 
from score as b 
where b.課程號 = a.課程號);

上面查詢結果課程號 “0001” 有 2 行數據，是因爲最大成績 80 有 2 個

分組取每組最小值

案例：按課程號分組取成績最小值所在行的數據

同樣的使用關聯子查詢來實現

select * 
from score as a 
where 成績 = (
select min(成績) 
from score as b 
where b.課程號 = a.課程號);

每組最大的 N 條記錄

案例：查詢各科成績前兩名的記錄

第 1 步，查出有哪些組

我們可以按課程號分組，查詢出有哪些組，對應這個問題裏就是有哪些課程號

select 課程號,max(成績) as 最大成績
from score 
group by 課程號;

第 2 步：先使用 order by 子句按成績降序排序（desc），然後使用 limt 子句返回 topN（對應這個問題返回的成績前兩名）

-- 課程號'0001' 這一組裏成績前2名
select * 
from score 
where 課程號 = '0001' 
order by 成績  desc 
limit 2;

同樣的，可以寫出其他組的（其他課程號）取出成績前 2 名的 sql

第 3 步，使用 union all 將每組選出的數據合併到一起

-- 左右滑動可以可拿到全部sql
(select * from score where 課程號 = '0001' order by 成績  desc limit 2)
union all
(select * from score where 課程號 = '0002' order by 成績  desc limit 2)
union all
(select * from score where 課程號 = '0003' order by 成績  desc limit 2);

前面我們使用 order by 子句按某個列降序排序（desc）得到的是每組最大的 N 個記錄。如果想要達到每組最小的 N 個記錄，將 order by 子句按某個列升序排序（asc）即可。

求 topN 的問題還可以使用自定義變量來實現，這個在後續再介紹。

如果對多表合併還不瞭解的，可以看下我講過的《從零學會 SQL》的 “多表查詢”。

總結

常見面試題：分組取每組最大值、最小值，每組最大的 N 條（top N）記錄。

4. 多表查詢

查詢所有學生的學號、姓名、選課數、總成績

select a.學號,a.姓名,count(b.課程號) as 選課數,sum(b.成績) as 總成績
from student as a left join score as b
on a.學號 = b.學號
group by a.學號;

查詢平均成績大於 85 的所有學生的學號、姓名和平均成績

select a.學號,a.姓名, avg(b.成績) as 平均成績
from student as a left join score as b
on a.學號 = b.學號
group by a.學號
having avg(b.成績)>85;

查詢學生的選課情況：學號，姓名，課程號，課程名稱

select a.學號, a.姓名, c.課程號,c.課程名稱
from student a inner join score b on a.學號=b.學號
inner join course c on b.課程號=c.課程號;

查詢出每門課程的及格人數和不及格人數

-- 考察case表達式
select 課程號,
sum(case when 成績>=60 then 1 
  else 0 
    end) as 及格人數,
sum(case when 成績 <  60 then 1 
  else 0 
    end) as 不及格人數
from score
group by 課程號;

使用分段 [100-85],[85-70],[70-60],[<60] 來統計各科成績，分別統計：各分數段人數，課程號和課程名稱

-- 考察case表達式
select a.課程號,b.課程名稱,
sum(case when 成績 between 85 and 100 
  then 1 else 0 end) as '[100-85]',
sum(case when 成績 >=70 and 成績<85 
  then 1 else 0 end) as '[85-70]',
sum(case when 成績>=60 and 成績<70  
  then 1 else 0 end) as '[70-60]',
sum(case when 成績<60 then 1 else 0 end) as '[<60]'
from score as a right join course as b 
on a.課程號=b.課程號
group by a.課程號,b.課程名稱;

查詢課程編號爲 0003 且課程成績在 80 分以上的學生的學號和姓名 |

select a.學號,a.姓名
from student  as a inner join score as b on a.學號=b.學號
where b.課程號='0003' and b.成績>80;

下面是學生的成績表（表名 score，列名：學號、課程號、成績）

使用 sql 實現將該錶行轉列爲下面的表結構

【面試題類型總結】這類題目屬於行列如何互換，解題思路如下：

【面試題】下面是學生的成績表（表名 score，列名：學號、課程號、成績）

使用 sql 實現將該錶行轉列爲下面的表結構

【解答】

第 1 步，使用常量列輸出目標表的結構

可以看到查詢結果已經和目標表非常接近了

select 學號,'課程號0001','課程號0002','課程號0003'
from score;

第 2 步，使用 case 表達式，替換常量列爲對應的成績

select 學號,
(case 課程號 when '0001' then 成績 else 0 end) as '課程號0001',
(case 課程號 when '0002' then 成績 else 0 end) as  '課程號0002',
(case 課程號 when '0003' then 成績 else 0 end) as '課程號0003'
from score;

在這個查詢結果中，每一行表示了某個學生某一門課程的成績。比如第一行是'學號 0001'選修'課程號 00001'的成績，而其他兩列的'課程號 0002'和'課程號 0003'成績爲 0。

每個學生選修某門課程的成績在下圖的每個方塊內。我們可以通過分組，取出每門課程的成績。

第 3 關，分組

分組，並使用最大值函數 max 取出上圖每個方塊裏的最大值

select 學號,
max(case 課程號 when '0001' then 成績 else 0 end) as '課程號0001',
max(case 課程號 when '0002' then 成績 else 0 end) as '課程號0002',
max(case 課程號 when '0003' then 成績 else 0 end) as '課程號0003'
from score
group by 學號;

這樣我們就得到了目標表（行列互換）

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/nhqM70-dHbuRH5kVEowM8A