sql经典面试题及答案大全_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

今天的文章sql经典面试题及答案大全_sql常见面试题分享到此就结束了，感谢您的阅读，如果确实帮到您，您可以动动手指转发给其他人。

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至举报，一经查实，本站将立刻删除。
如需转载请保留出处：https://bianchenghao.cn/50153.html

sql经典面试题及答案大全_sql常见面试题

相关推荐

发表回复