当前位置:首页 > 开发 > 数据库 > 正文

Each score of subjects is bigger than a number

发表于: 2011-05-08   作者:crabdave   来源:转载   浏览次数:
摘要: Each score of subjects is bigger than a number   SQL script:     SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for student -- -----------------

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!

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Each score of subjects is bigger than a number

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
1: What's the Z-Score Z-score formula for Predicting Bankruptcy of Edward Altman is a multivar
Slip on sneakers have been gaining in popularity during recent years. The casual look is in,
百度百科:http://baike.baidu.com/link?url=QKekCzlVSwL9zeJnAnxyGW5vHvcse0tqS7Dyp4vwALDiJxim1_C
需求: 相同的采购合同号对应的供应商应该相同,如下图,填写采购合同号,选中对应供应商,但相同合同号
获取index <div class="side-menu"> <h3 class="item">管理中心</h3> <div cl
作者:zccst //最简单的对象 var o = { 'name':"哈哈", 'age' : 30, 'level':"2b", 'sex' : '男' };
Validate if a given string is numeric. Some examples: "0" => true " 0.1 " => true "abc"
Question: Given an array of integers, every element appears twice except for one. Find that s
Determine whether an integer is a palindrome. Do this without extra space. Some hints: Could
引子: 最近遇到一个问题,就是在each()函数中怎么模拟for循环中的break和continue的操作。所以就查
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号