MySQL records for nearly two months are merged into a single line display

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