ID Type Name Age
t1 100 A 14
t1 101 B 15
t1 102 C 15
t1 103 D 12
t2 100 A1 14
t2 101 B1 15
t2 102 C1 15
t2 103 D1 12
t3 100 A2 14
t3 101 B2 15
t3 102 C2 15
t3 103 D2 12
在sql 查询时 如何将上面的结果变成，用case when then 的时候 总会有很多NULL，是按 Type 分类的，
ID Name0 Age0 Name1 Age1 Name2 Age2 Name3 Age3
t1 A 14 B 15 C 15 D 12
t2 A1 14 B1 15 C1 15 D1 12
t3 A2 14 B2 15 C2 15 D2 12
------解决方案--------------------
select ID,max(case when type=100 then Name end) as Name0,max(case when type=100 then age end) as age0,
max(case when type=101 then Name end) as Name1,max(case when type=101 then age end) as age1,
max(case when type=102 then Name end) as Name2,max(case when type=102 then age end) as age2,
....
from tt group by id
------解决方案--------------------
case when co1 is null then 0 else col1 end
------解决方案--------------------
mysql> SELECT ID,MAX(CASE WHEN TYPE=100 THEN NAME END) AS Name0,MAX(CASE WHEN TY
PE=100 THEN age END) AS age0,
-> MAX(CASE WHEN TYPE=101 THEN NAME END) AS Name1,MAX(CASE WHEN TYPE=101 THE
N age END) AS age1,
-> MAX(CASE WHEN TYPE=102 THEN NAME END) AS Name2,MAX(CASE WHEN TYPE=102 THE
N age END) AS age2,
-> MAX(CASE WHEN TYPE=103 THEN NAME END) AS Name3,MAX(CASE WHEN TYPE=103 THE
N age END) AS age3
-> FROM t4 GROUP BY id;
+------+-------+------+-------+------+-------+------+-------+------+
------解决方案--------------------