这条语句如何写
我把问题简化如下
现在有一个表
a b c d
1 45 1 china
1 45 2 American
2 99 1 German
2 99 2 Thailand
3 75 1 Austialia
3 75 2 USA
4 29 1 Singapore
4 29 2 Korean
.......我现在需要将这个表转换为以下的表 a b d1 d21
1 45 china American
2 99 German Thailand
3 75 Austialia USA
4 29 Singapore Korean
.......意思是做简单的行列转换,问如何写 ?
我把问题简化如下
现在有一个表
a b c d
1 45 1 china
1 45 2 American
2 99 1 German
2 99 2 Thailand
3 75 1 Austialia
3 75 2 USA
4 29 1 Singapore
4 29 2 Korean
.......我现在需要将这个表转换为以下的表 a b d1 d21
1 45 china American
2 99 German Thailand
3 75 Austialia USA
4 29 Singapore Korean
.......意思是做简单的行列转换,问如何写 ?
+------+------+------+-----------+
| a | b | c | d |
+------+------+------+-----------+
| 1 | 45 | 1 | china |
| 1 | 45 | 2 | American |
| 2 | 99 | 1 | German |
| 2 | 99 | 2 | Thailand |
| 3 | 75 | 1 | Austialia |
| 3 | 75 | 2 | USA |
| 4 | 29 | 1 | Singapore |
| 4 | 29 | 2 | Korean |
+------+------+------+-----------+
8 rows in set (0.00 sec)mysql> select a.a,a.b,a.d as d11,b.d as d21
-> from tx a , tx b
-> where a.a=b.a and a.b=b.b
-> and a.c=1 and b.c=2;
+------+------+-----------+----------+
| a | b | d11 | d21 |
+------+------+-----------+----------+
| 1 | 45 | china | American |
| 2 | 99 | German | Thailand |
| 3 | 75 | Austialia | USA |
| 4 | 29 | Singapore | Korean |
+------+------+-----------+----------+
4 rows in set (0.00 sec)mysql>
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
-> max(if(c=1,d,null)) as d11,
-> max(if(c=2,d,null)) as d21
-> from tx
-> group by a,b;
+------+------+-----------+----------+
| a | b | d11 | d21 |
+------+------+-----------+----------+
| 1 | 45 | china | American |
| 2 | 99 | German | Thailand |
| 3 | 75 | Austialia | USA |
| 4 | 29 | Singapore | Korean |
+------+------+-----------+----------+
4 rows in set (0.02 sec)mysql>