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

目錄

​

SQL基礎知識整理:

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

三、50道面試題

2.彙總統計分組分析

3.複雜查詢

sql面試題：topN問題

4.多表查詢

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

其他面試題:

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

)

--55. 更改 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;

--56. 删除 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語句。

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

一、建立資料庫和表

為了示範題目的運作過程，我們先按下面語句在用戶端navicat中建立資料庫和表。

（如何你還不懂什麼是資料庫，什麼是用戶端navicat，可以先學習這個：

1.建立表

1）建立學生表（student）

按下圖在用戶端navicat裡建立學生表

學生表的“學号”列設定為主鍵限制，下圖是每一列設定的資料類型和限制

建立完表，點選“儲存”

2）建立成績表（score）

同樣的步驟，建立"成績表“。“課程表的“學号”和“課程号”一起設定為主鍵限制（聯合主鍵），“成績”這一列設定為數值類型（float，浮點數值）

3）建立課程表（course）

課程表的“課程号”設定為主鍵限制

4）教師表（teacher）

教師表的“教師号”列設定為主鍵限制，

教師姓名這一列設定限制為“null”（紅框的地方不勾選），表示這一列允許包含空值（null）

2.向表中添加資料

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.多表查詢

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

selecta.學号,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 學号;
           

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

其他面試題:

https://blog.csdn.net/u010565545/article/details/100786293