mysql行转化列 ？该如何解决

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', col4, 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)mysql>
------解决方案-------------------- 
 
这样看来，我还算是没走错路~

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', col4, 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)mysql>
[解决办法]
http://blog.csdn.net/acmain_chm/article/details/4283943
MySQL交叉表
在某些数据库中有交叉表，但在MySQL中却没有这个功能，但网上看到有不少朋友想找出一个解决方法，特发贴集思广义。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198现整理解法如下：数据样本： create table tx(  id int primary key,  c1 c... 

 

查看更多 下一篇