关于数据库:常见-SQL-面试题经典-50-例

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);

  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
  ) 

  问题：返回比本部门平均工资高的员工的 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 语句。（搜寻公众号民工哥技术之路，回复“10241”，送你一份技术资源宝典）

ps：这些题考查 SQL 的编写能力，对于这类型的题目，须要你先把 4 张表之间的关联关系搞清楚了，最好的方法是本人在草稿纸上画出关联图，而后再编写对应的 SQL 语句就比拟容易了。下图是我画的这 4 张表的关系图，能够看出它们之间是通过哪些外键关联起来的：

一、创立数据库和表

为了演示题目的运行过程，咱们先按上面语句在客户端 navicat 中创立数据库和表。

如何你还不懂什么是数据库，什么是客户端 navicat，能够先学习这个：

1. 创立表

1）创立学生表（student）

按下图在客户端 navicat 里创立学生表。举荐：企业面试题汇总

学生表的“学号”列设置为主键束缚，下图是每一列设置的数据类型和束缚

创立完表，点击“保留”

2）创立成绩表（score）

同样的步骤，创立 ” 成绩表“。“课程表的“学号”和“课程号”一起设置为主键束缚（联结主键），“问题”这一列设置为数值类型（float，浮点数值）

3）创立课程表（course）

课程表的“课程号”设置为主键束缚

4）老师表（teacher）

老师表的“老师号”列设置为主键束缚，老师姓名这一列设置束缚为“null”（红框的中央不勾选），示意这一列容许蕴含空值（null）。举荐：企业面试题汇总

向表中增加数据

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）记录。

多表查问

查问所有学生的学号、姓名、选课数、总成绩

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 学号;

这样咱们就失去了指标表（行列调换）

作者：sh_c_2450957609
blog.csdn.net/u010565545/article/details/100785261