Mysql requirement scenario-column Wrap
As it is a simulation scene, go straight to the topic, table name and structure definition is very low, please ignore!
(1) the existing student scores are as follows:
Mysql > select * from vmark1
+-+
| | sid | sname | course | mark | |
+-+
| | 1 | Zhang San | jsj | 90 | |
| | 1 | Zhang San | yuwen | 65 | |
| | 1 | Zhang San | yingyu | 80 | |
| | 2 | Li Si | jsj | 80 | |
| | 2 | Li Si | yuwen | 98 |
| | 2 | Li Si | yingyu | 90 | |
+-+
6 rows in set (0.00 sec)
(2) the need is to look at students' scores in a more intuitive way. To refine is to display the values of the mark column as rows. Query through the case..when statement:
Mysql > select sid,sname,case when course='jsj' then mark end jsj,case when course='yuwen' then mark end yuwen,case when course='yingyu' then mark end yingyu from vmark1
+-+
| | sid | sname | jsj | yuwen | yingyu | |
+-+
| | 1 | Zhang San | 90 | NULL | NULL | |
| | 1 | Zhang San | NULL | 65 | NULL |
| | 1 | Zhang San | NULL | NULL | 80 | |
| | 2 | Li Si | 80 | NULL | NULL | |
| | 2 | Li Si | NULL | 98 | NULL |
| | 2 | Li Si | NULL | NULL | 90 | |
+-+
6 rows in set (0.01 sec)
Shit, it's all right. But it still looks very unfriendly!
(3) optimize by function and group by sentence:
Mysql > select sid,sname,sum (case when course='jsj' then mark end) jsj,sum (case when course='yuwen' then mark end) yuwen,sum (case when course='yingyu' then mark end) yingyu from vmark1 group by sid
+-+
| | sid | sname | jsj | yuwen | yingyu | |
+-+
| | 1 | Zhang San | 90 | 65 | 80 | |
| | 2 | Li Si | 80 | 98 | 90 | |
+-+
2 rows in set (0.00 sec)
"Wow, that's what you want. Don't move. That's it. The running girl said excitedly.
Okay. Over.
