Recently do report statistics, using the requirements to compare the performance of nearly two months, and make some chain data.
Scenario: Combine the average scores of the two-month language of 1 classmates into a single line comparison.
CREATE TABLE ' Chinese_score ' (
' id ' int (one) not NULL auto_increment,
' Name ' varchar (+) is not NULL,
' Score ' int (11),
' Date ' varchar (6),
PRIMARY KEY (' id ')
)
Insert a few students two months of results:
mysql> INSERT into Chinese_score value (null, ' Zhang San ', 91, ' 201506 ');
mysql> INSERT into Chinese_score value (NULL, ' John Doe ', 88, ' 201506 ');
mysql> INSERT into Chinese_score value (null, ' Lao Wang ', 80, ' 201506 ');
mysql> INSERT into Chinese_score value (null, ' Xiangzi ', 77, ' 201506 ');
mysql> INSERT into Chinese_score value (null, ' Zhang San ', 89, ' 201507 ');
mysql> INSERT into Chinese_score value (NULL, ' John Doe ', 85, ' 201507 ');
mysql> INSERT into Chinese_score value (null, ' Lao Wang ', 79, ' 201507 ');
mysql> INSERT into Chinese_score value (null, ' Xiangzi ', 82, ' 201507 ');
The comparison between the average scores of Chinese and the total score for the last two months is:
Mysql> Select AVG (if (date= ' 201506 ', score,null)) avg06, AVG (if (date= ' 201507 ', score,null)) avg07, sum (if (date= ') 201506 ', score,null)) sum06, sum (if (date= ' 201507 ', score,null)) sum07 from Chinese_score;
+---------+---------+-------+-------+
| avg06 | avg07 | sum06 | sum07 |
+---------+---------+-------+-------+
| 84.0000 | 83.7500 | 336 | 335 |
+---------+---------+-------+-------+
1 row in Set
The effect of AVG (if (date= ' 201506 ', score,null)) is equal to:
if (): If the value of date is 201506, the score of this record is added to AVG (score), otherwise null.
MySQL records for nearly two months are merged into a single line display