- 浏览: 1275872 次
- 性别:
- 来自: 北京
文章分类
- 全部博客 (471)
- Database (29)
- Java (47)
- Frameworks (9)
- JavaScript (30)
- Others (27)
- ExtJS (26)
- Linux (49)
- Windows (11)
- Web (8)
- Ubunt (9)
- Shell (21)
- MySQL (26)
- Redis (9)
- Git (6)
- Maven (29)
- Python (3)
- Nginx (10)
- Nodejs (7)
- Network (1)
- GO (2)
- Docker (36)
- MongoDB (5)
- Intellij idea (7)
- Ruby (3)
- Weblogic (3)
- CSS (15)
- VMware (3)
- Tomcat (6)
- Cache (2)
- PHP (8)
- Mac (7)
- jQuery (3)
- Spring (8)
- HTML5 (2)
- Kubernetes (8)
最新评论
-
masuweng:
Intellij idea 主题下载网址 -
mimicom:
还有一个情况, 也是连不上 2018-05-06T06:01: ...
docker-compose 部署shipyard -
lixuansong:
put()方法调用前必须先手动调用remove(),不然不会实 ...
JavaScript创建Map对象(转) -
jiao_zg22:
方便问下,去哪里下载包含Ext.ux.TabCloseMenu ...
Ext.ux.TabCloseMenu插件的使用(TabPanel右键关闭菜单) 示例 -
netwelfare:
对于基本类型的讲解,文章写的有点简单了,没有系统化,这篇文章介 ...
Java 基础类型范围
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!
发表评论
-
Srping配置阿里数据库连接池(Druid)
2015-10-19 13:49 2083Srping配置阿里数据库连接池(Druid) POM. ... -
Oracle 常用函数
2015-08-27 16:08 1080Oracle 常用函数 获取前10个小时的时间: sel ... -
Linux安装MYSQL(手工编译)
2015-08-02 14:22 3672Linux安装MYSQL(手工编译) 创建目录 ... -
Oracle 存储过程中打印SQL影响的行数
2015-07-07 11:41 1749Oracle 存储过程中打印SQL影响的行数 db ... -
更新多个字段的UPDATE语句
2015-06-19 16:18 4029更新多个字段的UPDATE语句 ... -
解决HSQL中java.lang.NoClassDefFoundError:java/sql/Savepoint错误
2011-07-16 09:35 1577解决HSQL中 java.lang.NoClassDefFo ... -
rank() function in application
2011-05-27 13:16 1404rank() function in application ... -
Oracle Statements
2011-05-23 09:33 633Oracle Statements --crea ... -
DB2 Statements
2011-04-14 16:58 1165DB2 Statements 生成1-8之间的随机整数 ... -
数据表纵转横
2010-01-26 15:18 1915数据表纵转横 方法一: --创建函数与类型 cre ... -
Oracle优化器Optimizer详解(转)
2010-01-13 11:24 2422Oracle优化器Optimizer详解(转) ... -
PreparedStatement中setString方法的异常(转载)
2010-01-08 11:00 2152PreparedStatement中setString方法的异 ... -
oracle 实现 split 函数
2010-01-05 10:46 3055oracle 实现 split 函数 CREATE O ... -
row_number()over函数的使用(转)
2009-12-16 16:05 1945row_number()over函数的使用(转) ... -
Oracle随机函数
2009-11-25 18:05 3926Oracle随机函数 --创建55到100之间随机数( ... -
Oracle取整的函数
2009-11-25 18:05 2917Oracle取整的函数 1.取整(大) sel ... -
Oracle rownum使用经验总结
2009-11-25 17:38 1617Oracle rownum使用经验总 ... -
Oracle TIMESTAMP 的使用
2009-11-13 17:15 4210Oracle TIMESTAMP 的使用 TIMEST ... -
使用预编译语句设置null值(preparedStatement.setObject)
2009-10-22 16:40 3278使用预编译语句设置null值(preparedStatemen ... -
ORACLE 常用命令
2009-10-20 15:52 1397ORACLE 常用命令 1.用命令行执行一个.sq ...
相关推荐
as “the quality of image Ia is better than that of image Ib” for training a robust BIQA model. The preference label, representing the relative quality of two images, is generally precise and ...
Each of those subjects is described in principle, and then applied through symbolic computing.The aim of the text is designed to clarify and optimize the efficiency of the students acquisition of ...
Correlational: The basic question for descriptive research is - "What are the values of a number of variables for a given sample of subjects. The basic research question for correlation research is - ...
Therefore, it is one of the goals of this chapter to provide the book content in such a way as to permit each reader to select those chapters of individual interest and to be aware that much of the ...
"Few mathematics books serve a wider potential readership than does a source book and this particular one is admirably designed to cater for a broad spectrum of tastes: professional mathematicians ...
The number of subjects in the NIR-VIS 2.0 database is 725, which is 3 times more than the HFB database. We define a group of specific protocols for performance evaluation. On the contrary, the ...
The goal of this book is to provide a focused examination of each of these topics, covering the essential information you need to fully exploit the power of the C++ language. Many of the topics in ...
While the problem is notoriously difficult, and complicated even to state carefully, it can be loosely formulated as "the number of integers with an even number of prime factors is the same as the ...
A final difference is the heavy capitalistic and marketeering influence in many of the chapters. This unplanned emphasis is at center stage in sections by Grant, Williams, Brown, and others, and ...
Dataset for human fall detection,该文件...Number of subjects38 subjects Number of ADLs19 Number of falls15 Subjects age19-75 Sensors used Triaxial accelerometer and gyroscope Position of sensor Waist
vide a focused examination of each of these topics, covering the essential information you need to fully exploit the power of the C++ language. Many of the topics in this book deserve a book in their ...
Comparison of the slosson intelligence test and wisc scores of subjects referred to a reading clinic COMPARISON OF THE SLOSSON INTELLIGENCE TEST AND WISC SCORES O F SUBJECTS REFERRED TO A READING ...
The role of this book is to bridge these two subjects by introducing how scientific computing can be done using the Python programming language and the computing environment that has appeared around ...
The implementation of numerical methods in MATLAB is integrated within each chapter and numerous examples in chemical engineering are provided, with a library of corresponding MATLAB programs....
photographs of the envelopes along with a record of the zip code that each envelope was addressed to. That is, within some context we can take a record of the actions of our subjects, learn from this ...
Technology suggests a number of issues: •A lack of relevant subject material in the curriculum. •STEM subjects are viewed as hard so that, in an environment where there is an emphasis on getting ...
It is intended for a broad range of readers. Indeed, RF and wireless communications is an extensive multidisciplinary field, one that includes many diverse subjects, from regulatory and ...
is typical for a mature scientific area, despite the dramatic progress a number of fundamental subjects have emerged. These topics, not much different from the material covered in the first edition, ...
Moreover, I believe that each of the subjects I interviewed for this book is indeed a data scientist—and, after having spent time with all of them, I couldn’t be more excited about the future of ...
Each of them contributed a number of comments and corrections to the text of this book. Suman Ganguli, Denis Hirschfeldt, and Joe Miller put a great effort into the notes of the course and made an ...