Mysql and mysql

Mysql and mysql

This article uses the SQL Sample Code to introduce how the mysql database converts rows and columns. Let's just look at the sample code.

Original table:

Table name: user -------------------------------------- name | course | grade limit zhangsan | Java | 70 rows zhangsan | C ++ | 80 rows lisi | java | 90 rows lisi | C # | 60 bytes ----------------------------------------

Use an SQL statement to obtain the following format:

----------------------------------------name   | java | C++ | C#----------------------------------------zhangsan | 70  | 80  | null----------------------------------------lisi   | 90  | null | 60----------------------------------------

Solution 1

select  name,sum(case when course='java' then grade end) as java,sum(case when course='C++' then grade end) as C++,sum(case when course='C#' then grade end) as C#from test group by name

Solution 2

select distinct c.`name` AS name,(select grade from test where name = c.`name` and course = 'java' )as java,(select grade from test where name = c.`name` and course = 'C++' )as C++,(select grade from test where name = c.`name` and course = 'C#' )as C#from test c

Summary

The above is all the content of this article. I hope this article will help you in your study or work. If you have any questions, please leave a message.