Each score of subjects is bigger than a number

Each score of subjects is bigger than a number

 

SQL script:

 

 

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for student
-- ----------------------------
CREATE TABLE `student` (
  `id` int(10) NOT NULL auto_increment,
  `name` varchar(50) default NULL,
  `subject` varchar(50) default NULL,
  `score` int(10) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

-- ----------------------------
-- Records 
-- ----------------------------
INSERT INTO `student` VALUES ('1', '张三', '数学', '50');
INSERT INTO `student` VALUES ('2', '张三', '语文', '100');
INSERT INTO `student` VALUES ('3', '张三', '英语', '90');
INSERT INTO `student` VALUES ('4', '张三', '化学', '60');
INSERT INTO `student` VALUES ('5', '李四', '语文', '81');
INSERT INTO `student` VALUES ('6', '李四', '英语', '90');
INSERT INTO `student` VALUES ('7', '李四', '化学', '88');
INSERT INTO `student` VALUES ('8', '李四', '历史', '66');
INSERT INTO `student` VALUES ('9', '李四', '数学', '79');
INSERT INTO `student` VALUES ('10', '王五', '数学', '100');
INSERT INTO `student` VALUES ('11', '王五', '历史', '88');
INSERT INTO `student` VALUES ('12', '王五', '英语', '66');
INSERT INTO `student` VALUES ('13', '王五', '化学', '78');
INSERT INTO `student` VALUES ('14', '王五', '物理', '80');
INSERT INTO `student` VALUES ('15', '小红', '英语', '88');
INSERT INTO `student` VALUES ('16', '李四', '物理', '69');

 

 

mysql> select * from student;
+----+------+---------+-------+
| id | name | subject | score |
+----+------+---------+-------+
|  1 | 张三 | 数学    |    50 |
|  2 | 张三 | 语文    |   100 |
|  3 | 张三 | 英语    |    90 |
|  4 | 张三 | 化学    |    60 |
|  5 | 李四 | 语文    |    81 |
|  6 | 李四 | 英语    |    90 |
|  7 | 李四 | 化学    |    88 |
|  8 | 李四 | 历史    |    66 |
|  9 | 李四 | 数学    |    79 |
| 10 | 王五 | 数学    |   100 |
| 11 | 王五 | 历史    |    88 |
| 12 | 王五 | 英语    |    66 |
| 13 | 王五 | 化学    |    78 |
| 14 | 王五 | 物理    |    80 |
| 15 | 小红 | 英语    |    88 |
| 16 | 李四 | 物理    |    69 |
+----+------+---------+-------+
16 rows in set

 

Changing the form of table:

 

select name,
  max(case when subject = '语文' then score else 0 end) as  '语文',
  max(case when subject = '数学' then score else 0 end) as  '数学',
  max(case when subject = '英语' then score else 0 end) as  '英语',
  max(case when subject = '历史' then score else 0 end) as  '历史',
  max(case when subject = '物理' then score else 0 end) as  '物理',
  max(case when subject = '化学' then score else 0 end) as  '化学'
from student
group by name;

 

  +------+------+------+------+------+------+------+
| name | 语文 | 数学 | 英语 | 历史 | 物理 | 化学 |
+------+------+------+------+------+------+------+
| 李四 |   81 |   79 |   90 |   66 |   69 |   88 |
| 王五 |    0 |  100 |   66 |   88 |   80 |   78 |
| 小红 |    0 |    0 |   88 |    0 |    0 |    0 |
| 张三 |  100 |   50 |   90 |    0 |    0 |   60 |
+------+------+------+------+------+------+------+
4 rows in set

 SQL Script:

select * from 
(select name,
  max(case when subject = '语文' then score else 0 end) as  '语文',
  max(case when subject = '数学' then score else 0 end) as  '数学',
  max(case when subject = '英语' then score else 0 end) as  '英语',
  max(case when subject = '历史' then score else 0 end) as  '历史',
  max(case when subject = '物理' then score else 0 end) as  '物理',
  max(case when subject = '化学' then score else 0 end) as  '化学'
from student
group by name) a
where a.语文>60 and a.数学>60 and a.英语>60 and a.历史>60 and a.物理>60 and a.化学>60 ;

 

+------+------+------+------+------+------+------+
| name | 语文 | 数学 | 英语 | 历史 | 物理 | 化学 |
+------+------+------+------+------+------+------+
| 李四 |   81   |      79 |      90 |      66 |      69 |   88 |
+------+------+------+------+------+------+------+
1 row in set

 

 

Done!