SQL经典面试题及答案

1. 用一条SQL 语句 查询出每门课都大于80 分的学生姓名
name kecheng fenshu
张三 语文 81
张三 数学 75

张三 英语 78

李四 语文 76

李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90

每门课大于80分就是语数英的分数都过80分，

如果不考虑学生的课程少录入情况（比如张三只有2个课程，王五有3个课程）

 select name from ims_ewei_score group by name having  min(fenshu)>80;

如果考虑学生的课程数大于等于3的情况

select name from ims_ewei_score group by name having count(kecheng) >=3 and min(fenshu)>80; //按照name 分组 group by 分组后再having 过滤 

 

查询去重，查询的数据不显示重复的

select distinct name from ims_ewei_score ;

select name from ims_ewei_score group by name;

 如下图，8、13条数据重复删除13条数据

 

 delete from ims_ewei_score where id not in (

   select tmp.id from

   (select min(id) as id from ims_ewei_score group by name,kecheng,fenshu)

  as temp

);

/*
 Navicat Premium Data Transfer

 Source Server         : bbk
 Source Server Type    : MySQL
 Source Server Version : 50649
 Source Host           : localhost:3306
 Source Schema         : bbk

 Target Server Type    : MySQL
 Target Server Version : 50649
 File Encoding         : 65001

 Date: 06/11/2020 20:50:59
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for ims_ewei_score
-- ----------------------------
DROP TABLE IF EXISTS `ims_ewei_score`;
CREATE TABLE `ims_ewei_score`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0',
  `kecheng` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0',
  `fenshu` int(11) NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of ims_ewei_score
-- ----------------------------
INSERT INTO `ims_ewei_score` VALUES (1, '张三', '语文', 81);
INSERT INTO `ims_ewei_score` VALUES (2, '张三', '数学', 75);
INSERT INTO `ims_ewei_score` VALUES (3, '李四', '语文', 100);
INSERT INTO `ims_ewei_score` VALUES (4, '李四', '数学', 81);
INSERT INTO `ims_ewei_score` VALUES (5, '王五', '数学', 99);
INSERT INTO `ims_ewei_score` VALUES (6, '王五', '英语', 98);
INSERT INTO `ims_ewei_score` VALUES (7, '王五', '语文', 81);
INSERT INTO `ims_ewei_score` VALUES (8, '张三', '英语', 78);
INSERT INTO `ims_ewei_score` VALUES (13, '张三', '英语', 78);

SET FOREIGN_KEY_CHECKS = 1;

1、列出各门课程成绩最好的学生(要求显示字段:姓名,科目，成绩)  //首先列出各科最高的分数

select t1.id,t1.name,t1.kecheng,t1.fenshu from ims_ewei_score t1,
(select kecheng,max(fenshu) as maxfenshu from ims_ewei_score group  by kecheng) as t2
where t2.kecheng=t1.kecheng and t2.maxfenshu=t1.fenshu;

2、计算每个人的平均成绩（要求显示字段: 姓名，平均成绩）

select name,avg(fenshu) as avgfenshu from ims_ewei_score group by name;

3、计算每个人单科的最高成绩(要求显示字段: 姓名，课程，最高成绩)

select t1.name,t1.fenshu,t1.kecheng from ims_ewei_score t1,
(select name,max(fenshu) as maxfenshu from ims_ewei_score group by name)t2
where t2.name=t1.name and t2.maxfenshu=t1.fenshu;

4、.计算每个人的总成绩并排名(要求显示字段: 姓名，总成绩) 

select name,sum(fenshu) sumfenshu from ims_ewei_score group by name order by sumfenshu;
    

5、列出各门课程成绩最好的两位学生(要求显示字段: 姓名,科目，成绩) 有两种方法

方法一:
select  t1.* from ims_ewei_score t1 where t1.name in (
select  TOP 2 name from ims_ewei_score where kecheng = t1.kecheng order by fenshu desc)
order by t1.kecheng;
方法二:

select t1.name,t1.fenshu,t1.kecheng from ims_ewei_score t1,
(select kecheng,max(fenshu) as maxfenshu from ims_ewei_score group by fenshu order by maxfenshu desc limit 2)t2
where t2.kecheng=t1.kecheng and t2.maxfenshu=t1.fenshu;

6、统计如下：

姓名	语文	数学	英语	总分	平均分

select name 姓名,sum(case when kecheng='语文' then fenshu else 0 end)as 语文,
sum(case when kecheng='数学' then fenshu else 0 end)as 数学,
sum(case when kecheng='英语' then fenshu else 0 end)as 英语,
SUM(fenshu)总分,avg(fenshu)平均分 from ims_ewei_score
group by name order by 总分;


sum（case where kecheng='数学' then fenshu else 0 end）
意思是当kecheng=’数学‘ 计算fenshu的和 否是 fenshu是0

7、列出各门课程的平均成绩（要求显示字段：课程，平均成绩）

select kecheng, avg(fenshu) 平均成绩 from ims_ewei_score group by kecheng;

8、列出数学成绩的排名（要求显示字段：姓名，成绩，排名）

select name,fenshu,
(select count(*) from ims_ewei_score t1 where kecheng='数学' and t1.fenshu >t2.fenshu)+1 as 名次 from ims_ewei_score t2
where kecheng='数学' order by fenshu desc;

--注释：排序，比较大小，比较的次数+1 = 排名。

9、列出数学成绩在2-3名的学生（要求显示字段：姓名,科目，成绩）

 select t3.*  from (
 select name,kecheng,fenshu,
(select count(*) from ims_ewei_score  t1 where kecheng ='数学' and t1.fenshu > t2.fenshu)+1 as 名次 from
 ims_ewei_score t2  where kecheng='数学') t3 
 where t3.名次 between 2 and 3 order by t3.fenshu desc;

10、求出李四的数学成绩的排名 

select name,fenshu,
(select count(*) from ims_ewei_score t1 where kecheng='数学' and t1.fenshu >t2.fenshu)+1 as 名次 from ims_ewei_score t2
where kecheng='数学' and name='李四' order by fenshu desc;

11、统计如下

select kecheng 科目,sum(case when fenshu between 0 and 59 then 1 else 0 end) as 不及格,
 sum(case when fenshu between 60 and 80 then 1 else 0 end) as 良,
 sum(case when fenshu between 81 and 100 then 1 else 0 end) as 优秀 from ims_ewei_score
 group by kecheng;

查询结果

 12、

统计如下：

 

数学: 张三(50分),李四(90分),王五(90分),赵六(76分) 

 declare @s varchar(1000)
 set @s=''
 select @s =@s+','+name+'('+convert(varchar(10),fenshu)+'分)' from 
 ims_ewei_score where kecheng='数学'
 set @s=stuff(@s,1,1,' ')print '数学:'+@s