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|李四|化学|88|8|李四|歴史|66|9|李四|数学|79|10|王五|数学|100|11|王五|歴史|88|12|王五|英語|66|13|王五|化学|78|14|王五|物理|80|15|小紅|英語|88|李四|物理|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|0|0|0|0|0|00|0|張三|100|50|90|0|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 ;

 
+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+--------+------------------------------------------------------------+--------+------------+--------+-----------------------------------------------------------
 
 
Done!