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...
SELECT CLASS, (SELECT GROUP_CONCAT(NAME) WHERE DATE=`2012-01-01`) AS `2012-01-01`, (SELECT GROUP_CONCAT(NAME) WHERE DATE=`2012-01-02`) AS `2012-01-02`, (SELECT GROUP_CONCAT(NAME) WHERE DATE=`2012-01-03`) AS `2012-01-03` FROM TT GROUP BY CLASS
希望这不是你所想要的。4. 动态,适用于列不确定情况,mysql> SET @EE=''; mysql> SELECT @EE:=CONCAT(@EE,'SUM(IF(C2=/'',C2,'/'',',C3,0)) AS ',C2,',') FROM (SELECT DISTINCT C2 FROM TX) A; mysql> SET @QQ=CONCAT('SELECT ifnull(c1,/'total/'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP'); Query OK, 0 rows affected (0.00 sec)mysql> PREPARE stmt2 FROM @QQ; Query OK, 0 rows affected (0.00 sec) Statement preparedmysql> EXECUTE stmt2; +--------------------+------+------+------+------+-------+ | ifnull(c1,'total') | B1 | B2 | B3 | B4 | TOTAL | +--------------------+------+------+------+------+-------+ | A1 | 9 | 2 | 1 | 11 | 23 | | A2 | 7 | 9 | 8 | 7 | 31 | | A3 | 4 | 8 | 8 | 8 | 28 | | A4 | 2 | 5 | 6 | 14 | 27 | | total | 22 | 24 | 23 | 40 | 109 | +--------------------+------+------+------+------+-------+ 5 rows in set (0.00 sec)mysql>
用SP生成 (SELECT GROUP_CONCAT(NAME) WHERE DATE=`2012-01-03`) AS `2012-01-03` 中的`2012-01-03` 打开SELECT DISTINCT DATE FROM TT游标,循环、动态生成SQL语句。
已自行解决。 我希望斑竹能把下面的代码作为单独的一个解决方案----专门针对非数字类交叉表的生成。set @EE=''; SELECT @EE:=CONCAT(@EE,'max(IF(date=\'',date,'\'',',name,0)) AS \'',date,'\',') FROM (SELECT DISTINCT date FROM 表 GROUP BY date) a; SET @QQ=CONCAT('SELECT ifnull(class,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),' FROM 表GROUP BY class with rollup'); prepare stmt2 from @QQ; execute stmt2;
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...
(SELECT GROUP_CONCAT(NAME) WHERE DATE=`2012-01-01`) AS `2012-01-01`,
(SELECT GROUP_CONCAT(NAME) WHERE DATE=`2012-01-02`) AS `2012-01-02`,
(SELECT GROUP_CONCAT(NAME) WHERE DATE=`2012-01-03`) AS `2012-01-03`
FROM TT GROUP BY CLASS
希望这不是你所想要的。4. 动态,适用于列不确定情况,mysql> SET @EE='';
mysql> SELECT @EE:=CONCAT(@EE,'SUM(IF(C2=/'',C2,'/'',',C3,0)) AS ',C2,',') FROM (SELECT DISTINCT C2 FROM TX) A; mysql> SET @QQ=CONCAT('SELECT ifnull(c1,/'total/'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP');
Query OK, 0 rows affected (0.00 sec)mysql> PREPARE stmt2 FROM @QQ;
Query OK, 0 rows affected (0.00 sec)
Statement preparedmysql> EXECUTE stmt2;
+--------------------+------+------+------+------+-------+
| ifnull(c1,'total') | B1 | B2 | B3 | B4 | TOTAL |
+--------------------+------+------+------+------+-------+
| A1 | 9 | 2 | 1 | 11 | 23 |
| A2 | 7 | 9 | 8 | 7 | 31 |
| A3 | 4 | 8 | 8 | 8 | 28 |
| A4 | 2 | 5 | 6 | 14 | 27 |
| total | 22 | 24 | 23 | 40 | 109 |
+--------------------+------+------+------+------+-------+
5 rows in set (0.00 sec)mysql>
(SELECT GROUP_CONCAT(NAME) WHERE DATE=`2012-01-03`) AS `2012-01-03`
中的`2012-01-03`
打开SELECT DISTINCT DATE FROM TT游标,循环、动态生成SQL语句。
你这个帖子我已经看过多遍,真心用不了。
我告诉你原因吧,因为你的这个帖子从头到尾都是在求和sum()。我不是要求和,我是要让字符连接。
我希望斑竹能把下面的代码作为单独的一个解决方案----专门针对非数字类交叉表的生成。set @EE='';
SELECT @EE:=CONCAT(@EE,'max(IF(date=\'',date,'\'',',name,0)) AS \'',date,'\',') FROM (SELECT DISTINCT date FROM 表 GROUP BY date) a;
SET @QQ=CONCAT('SELECT ifnull(class,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),' FROM 表GROUP BY class with rollup');
prepare stmt2 from @QQ;
execute stmt2;