原表:
表名 :user
name course grade
zhangsan java 70
zhangsan
C++
80
lisi java 90
lisi C# 60
用一条 SQL 语句得到如下形式:
name java C++ C#
zhangsan 70 80 null
lisi 90 null 60
分析:这是典型的行列转换问题,现给出两种转换语句:
SQL1:
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
SQL2:
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