sql 关于学生成绩相关查询

最新推荐文章于 2024-12-09 10:10:27 发布

ManrayHsu

最新推荐文章于 2024-12-09 10:10:27 发布

阅读量580

点赞数

分类专栏： mysql 文章标签： mysql 行变列

本文链接：https://blog.youkuaiyun.com/x386277405/article/details/51635319

版权

mysql 专栏收录该内容

2 篇文章

订阅专栏

-- 课程表

CREATE TABLE `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `tid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- 插入数据
insert  into `course`(`id`,`name`,`tid`) values (1,'Java',1),(2,'C++',1),(3,'C#',2);

-- 学生表
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  `age` tinyint(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- 插入数据
insert  into `student`(`id`,`name`,`sex`,`age`) values (1,'张三',1,23),(2,'李四',2,32),(3,'王五',1,33),(4,'阿六',2,22);

-- 学生成绩表
CREATE TABLE `student_course` (
  `sid` int(11) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  `score` decimal(6,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 插入数据
insert  into `student_course`(`sid`,`cid`,`score`) values (1,1,'78.00'),(1,2,'89.00'),(1,3,'99.00'),(2,1,'99.00'),(2,2,'56.00'),(2,3,'56.00'),(3,1,'44.00'),(3,2,'22.00'),(3,3,'21.00'),(3,3,'59.00');

-- 老师表
CREATE TABLE `teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

--插入数据
insert  into `teacher`(`id`,`name`) values (1,'马云'),(2,'马化腾');

1.查询java成绩高于c#的学生

SELECT xx.name,xx.java,xx.c1,xx.c2 FROM (
SELECT s.name, MAX(CASE c.`name` WHEN 'java' THEN sc.`score` ELSE 0 END) AS java,
MAX(CASE c.`name` WHEN 'C#' THEN IFNULL(sc.`score`,0) ELSE 0 END) AS c1,
MAX(CASE c.`name` WHEN 'C++' THEN IFNULL(sc.`score`,0) ELSE 0 END) AS c2
FROM student s
LEFT JOIN student_course sc ON sc.`sid`=s.`id`
LEFT JOIN course c ON c.id=sc.`cid`
GROUP BY s.`id`) xx
WHERE xx.java>xx.c1

2.查询平均成绩大于80的学生及平均分

SELECT s.name,FORMAT(AVG(sc.score),2)
FROM student s
LEFT JOIN student_course sc ON sc.`sid`=s.`id`
LEFT JOIN course c ON c.id=sc.`cid`
GROUP BY s.`id`
HAVING AVG(sc.score)>80

3.查询没科都没有几个的学生

SELECT s.name, MAX(CASE c.`name` WHEN 'java' THEN sc.`score` ELSE 0 END) AS java,
MAX(CASE c.`name` WHEN 'C#' THEN IFNULL(sc.`score`,0) ELSE 0 END) AS c1,
MAX(CASE c.`name` WHEN 'C++' THEN IFNULL(sc.`score`,0) ELSE 0 END) AS c2
FROM student s
LEFT JOIN student_course sc ON sc.`sid`=s.`id`
LEFT JOIN course c ON c.id=sc.`cid`
WHERE sc.score<60
GROUP BY s.id
HAVING COUNT(s.id)=3

4.查询参加马云老师课程的学生

SELECT s.name,t.name
FROM student s
RIGHT JOIN student_course sc ON sc.`sid`=s.`id`
RIGHT JOIN course c ON c.id=sc.`cid`
RIGHT JOIN teacher t ON t.id=c.tid
WHERE t.name='马云'
GROUP BY s.id

5.查询没有参加马云老师课程的学生

SELECT s.name FROM student s
WHERE s.id NOT IN(
SELECT s.id
FROM student s
RIGHT JOIN student_course sc ON sc.`sid`=s.`id`
RIGHT JOIN course c ON c.id=sc.`cid`
RIGHT JOIN teacher t ON t.id=c.tid
WHERE t.name!='马云'
GROUP BY s.id)