天使不喜欢看代码

这么好的文章没办法收藏。。（英语圈子中发 ... | 作个游戏：如果你下一周每天有30分钟空闲时 ...

2007-09-25

一维表变二维表。

关键字: SQL

数据库里面的值

1 50 a
1 22 b
1 33 c
2 11 a
2 12 b
2 24 c

我要做成这样的
a b c
1 50 22 33
2 11 12 24

你知道这样的sql怎么写吗？

mysql> select * from teamtest;
+------+------+------+
| id | name | type |
+------+------+------+
| 1 | mao | a |
| 1 | da | b |
| 1 | jun | c |
| 2 | java | a |
| 2 | eye | b |
| 2 | com | c |
+------+------+------+
6 rows in set (0.00 sec)

mysql> select aa.id ,aa.name , bb.name,cc.name
-> from (select id, name from teamtest where type='a') aa ,
-> (select id , name from teamtest where type ='b') bb,
-> (select id , name from teamtest where type ='c') cc
-> where aa.id = bb.id and bb.id =cc.id;
+------+------+------+------+
| id | name | name | name |
+------+------+------+------+
| 1 | mao | da | jun |
| 2 | java | eye | com |
+------+------+------+------+

select dd.id ,aa.name , bb.name,cc.name
from (select id, name from teamtest where type='a') aa ,
(select id , name from teamtest where type ='b') bb,
(select id , name from teamtest where type ='c') cc,
(select id from teamtest group by id ) dd ,
dd left jion in aa
where dd.id = aa.id or dd.id = bb.id or dd.id = cc.id