MySQL 行 列转换

CREATE TABLE `grade` (

  `name` varchar(255) NOT NULL,

  `class` varchar(255) NOT NULL,

  `score` tinyint(4) NOT NULL,

  `id` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

 

 

INSERT INTO `grade` VALUES ('张三', '语文', '81', '1');

INSERT INTO `grade` VALUES ('张三', '数学', '75', '2');

INSERT INTO `grade` VALUES ('李四', '语文', '76', '3');

INSERT INTO `grade` VALUES ('李四', '数学', '90', '4');

INSERT INTO `grade` VALUES ('王五', '语文', '81', '5');

INSERT INTO `grade` VALUES ('王五', '数学', '100', '6');

INSERT INTO `grade` VALUES ('王五', '英语', '90', '7');

INSERT INTO `grade` VALUES ('张三', '数学', '75', '8');

INSERT INTO `grade` VALUES ('李四', '语文', '76', '9');

 

1 用一条SQL语句查询出每门课都大于80分的学生姓名

select * from grade GROUP BY name HAVING  min(score)>80

 

 

2 用一条SQL,将以上表格的数据显示为【姓名 语文 数学 英语】

 

select name,
max(if(class = '数学', score, 0)) ,
max(if(class = '英语', score, 0)) ,
max(if(class = '语文', score, 0)) from grade
group by name;
select name 姓名,
max(case class when '语文' then score else 0 end) 语文,
max(case class when '数学' then score else 0 end) 数学,
max(case class when '英语' then score else 0 end) 英语
from grade
group by name

 

3 删除除了自动编号不同,其他都相同的学生冗余信息

delete from grade  where id not in(select mid from (select min(id) mid

from grade group by name,class) as t);

 

4  取出各科成绩前3名的学生、课程、成绩信息？

select a.class,a.score,a.`name`

from grade a

where (select count(*) from grade where class="a".class and a.score<score)<3

GROUP BY class,`name`  order by a.class,a.score desc;