mysql行转化列 ?
SQL code
col1 col2 col3 col4
a a1 a2 1.2589
a a1 a2 2.2510
a a1 a2 5.2502
a a1 a2 1.0058
a a1 a2 6.2589
b a1 a2 7.2589
b a1 a2 3.1247
b a1 a2 1.3652
b a1 a2 2.2589
b a1 a2 1.2589
c a1 a2 1.7278
c a1 a2 2.4989
c a1 a2 1.2589
c a1 a2 3.1509
c a1 a2 1.2500
d a1 a2 4.2899
d a1 a2 1.2785
d a1 a2 5.2587
d a1 a2 1.2536
d a1 a2 1.2471
得到样式如下:
col2 col3 col4 col5 col6 col7
a1 a2 1.2589 7.2589 1.7278 4.2899
a1 a2 2.2510 3.1247 2.4989 1.2785
a1 a2 5.2502 1.3652 1.2589 5.2587
a1 a2 1.0058 2.2589 3.1509 1.2536
a1 a2 6.2589 1.2589 1.2500 1.2471
转化条件为Col1 列中的值,每个col1 中 都有固定五列相同的值 。
------解决思路----------------------
弄了一个很笨拙的,不好意思出手,还是先看看高手的办法吧。
------解决思路----------------------
http://nowtbin.iteye.com/blog/684126
------解决思路----------------------
SET @a='';
SET @b=1;
SELECT col2,col3,xh,
SUM(IF(bz=1,col4,0)),MAX(IF(bz=2,col4,0)),MAX(IF(bz=3,col4,0)),
SUM(IF(bz=4,col4,0))
FROM (
SELECT *,CEILING(id/5) AS bz,@b:=IF(@a=col1,@b+1,1) AS xh,@a:=col1 FROM ttw ORDER BY xh,col1,col2) a
GROUP BY col2,col3,xh
------解决思路----------------------
这样看来,我还算是没走错路~
SQL code
mysql> select * from coltest;
+------+------+------+---------+
| col1 | col2 | col3 | col4 |
+------+------+------+---------+
| a | a1 | a2 | 1.25890 |
| a | a1 | a2 | 2.25100 |
| a | a1 | a2 | 5.25020 |
| a | a1 | a2 | 1.00580 |
| a | a1 | a2 | 6.25890 |
| b | a1 | a2 | 7.25890 |
| b | a1 | a2 | 3.12470 |
| b | a1 | a2 | 1.36520 |
| b | a1 | a2 | 2.25890 |
| b | a1 | a2 | 1.25890 |
| c | a1 | a2 | 1.72780 |
| c | a1 | a2 | 2.49890 |
| c | a1 | a2 | 1.25890 |
| c | a1 | a2 | 3.15090 |
| c | a1 | a2 | 1.25000 |
| d | a1 | a2 | 4.28990 |
| d | a1 | a2 | 1.27850 |
| d | a1 | a2 | 5.25870 |
| d | a1 | a2 | 1.25360 |
| d | a1 | a2 | 1.24710 |
+------+------+------+---------+
20 rows in set (0.00 sec)
mysql> set @xxxx := "";
Query OK, 0 rows affected (0.00 sec)
mysql> set @xx := 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select col2, col3, sum(col4), sum(col5), sum(col6), sum(col7)
-> from
-> (
-> select if(col1 = @xxxx, @xx:=@xx+1, @xx := 0) as xx, col1, col2, col3
, if(col1='a', col4, 0) as col4, if(col1='b', col4, 0) as col5, if(col1='c', col
4, 0) as col6, if(col1='d', col4, 0) as col7, @xxxx := col1
-> from coltest
-> order by col1
-> ) as x
-> group by x.col2, x.col3, x.xx
-> ;
+------+------+-----------+-----------+-----------+-----------+
| col2 | col3 | sum(col4) | sum(col5) | sum(col6) | sum(col7) |
+------+------+-----------+-----------+-----------+-----------+
| a1 | a2 | 1.25890 | 1.25890 | 1.25000 | 4.28990 |
| a1 | a2 | 2.25100 | 2.25890 | 3.15090 | 1.27850 |
| a1 | a2 | 5.25020 | 1.36520 | 1.25890 | 5.25870 |
| a1 | a2 | 1.00580 | 3.12470 | 2.49890 | 1.25360 |
| a1 | a2 | 6.25890 | 7.25890 | 1.72780 | 1.24710 |
+------+------+-----------+-----------+-----------+-----------+
5 rows in set (0.00 sec)
--转自