Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.