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!
