mysql交叉表问题

Question

本帖最后由 lazygc520 于 2012-02-13 09:20:41 编辑

Answer 1

测试数据：id  time  type  count
5333  2012-02-08 08  ETC56-542N  43
5406  2012-02-08 09  ETC56-542N  37
5488  2012-02-08 10  ETC54-623N  16
5519  2012-02-08 10  ETC54-628N  22
5556  2012-02-08 11  ETC54-628N  31
5617  2012-02-08 12  ETC54-628N  55
5736  2012-02-08 13  ETC56-605N  27
5717  2012-02-08 13  ETC54-628N  4
5778  2012-02-08 14  ETC56-605N  49
5872  2012-02-08 15  ETC56-605N  52
5984  2012-02-08 16  ETC56-605N  16
6015  2012-02-08 20  ETC56-605N  59
6119  2012-02-08 21  ETC56-605N  60
6239  2012-02-08 22  ETC56-605N  25
6294  2012-02-08 22  ETC56-600N  41
6365  2012-02-08 23  ETC56-600N  39
6449  2012-02-09 00  ETC56-600N  81
6605  2012-02-09 01  ETC56-600N  41
6676  2012-02-09 02  ETC56-600N  64
6800  2012-02-09 03  ETC56-600N  72
6947  2012-02-09 04  ETC56-600N  30

Answer 2

SET @QQ=CONCAT('SELECT ifnull(TIME,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(COUNT) AS TOTAL FROM BARCODE_2D_TIME GROUP BY TIME WITH ROLLUP');
select @qq;PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;
看一下生成的SQL语句是什么样，应该是有语法错误。

Answer 3

前面三条语句执行都没问题，但是PREPARE stmt2 FROM @QQ;这条语句执行时就报错。#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-542N,SUM(IF(TYPE='ETC54-623N',COUNT,0)) AS ETC54-623N,SUM(IF(TYPE='ETC54-628N',' at line 1 怀疑是否是因为统计数量为0造成的？

Answer 4

执行sql语句SET @EE='';
SELECT @EE:=CONCAT(@EE,'SUM(IF(TYPE=\'',TYPE,'\'',',COUNT,0)) AS ',TYPE,',') FROM (SELECT DISTINCT TYPE FROM `BARCODE_2D_TIME`) A;
SET @QQ=CONCAT('SELECT ifnull(TIME,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(COUNT) AS TOTAL FROM BARCODE_2D_TIME GROUP BY TIME WITH ROLLUP');
select @qq;
得到结果：mysql> SET @EE='';
Query OK, 0 rows affected (0.00 sec)mysql> SELECT @EE:=CONCAT(@EE,'SUM(IF(TYPE=\'',TYPE,'\'',',COUNT,0)) AS ',TYPE,'
,') FROM (SELECT DISTINCT TYPE FROM `HAPSU`.`BARCODE_2D_TIME`) A;
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------+
| @EE:=CONCAT(@EE,'SUM(IF(TYPE=\'',TYPE,'\'',',COUNT,0)) AS ',TYPE,',')
        |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------+
| SUM(IF(TYPE='ETC56-542N',COUNT,0)) AS ETC56-542N,
        |
| SUM(IF(TYPE='ETC56-542N',COUNT,0)) AS ETC56-542N,SUM(IF(TYPE='ETC54-623N',COUN
T,0)) AS ETC54-623N,        |
| SUM(IF(TYPE='ETC56-542N',COUNT,0)) AS ETC56-542N,SUM(IF(TYPE='ETC54-623N',COUN
T,0)) AS ETC54-623N,SUM(IF(TYPE='ETC54-628N',COUNT,0)) AS ETC54-628N,        |
| SUM(IF(TYPE='ETC56-542N',COUNT,0)) AS ETC56-542N,SUM(IF(TYPE='ETC54-623N',COUN
T,0)) AS ETC54-623N,SUM(IF(TYPE='ETC54-628N',COUNT,0)) AS ETC54-628N,SUM(IF(TYPE
='ETC56-605N',COUNT,0)) AS ETC56-605N,
        |
| SUM(IF(TYPE='ETC56-542N',COUNT,0)) AS ETC56-542N,SUM(IF(TYPE='ETC54-623N',COUN
T,0)) AS ETC54-623N,SUM(IF(TYPE='ETC54-628N',COUNT,0)) AS ETC54-628N,SUM(IF(TYPE
='ETC56-605N',COUNT,0)) AS ETC56-605N,SUM(IF(TYPE='ETC56-600N',COUNT,0)) AS ETC5
6-600N, |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------+
5 rows in set (0.01 sec)mysql> SET @QQ=CONCAT('SELECT ifnull(TIME,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(COUNT) AS TOTAL FROM `HAPSU`.`BARCODE_2D_TIME` GROUP BY TIME WI
H ROLLUP');
Query OK, 0 rows affected (0.00 sec)mysql> select @qq;
+----------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------+
| @qq                                                     |
+----------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------+
| SELECT ifnull(TIME,'total'),SUM(IF(TYPE='ETC56-542N',COUNT,0)) AS ETC56-542N,SUM(IF(TYPE='ETC54-623N',COUNT,0)) AS ETC54-623N,SUM(IF(TYPE='ETC54-62
N',COUNT,0)) AS ETC54-628N,SUM(IF(TYPE='ETC56-605N',COUNT,0)) AS ETC56-605N,SUM(IF(TYPE='ETC56-600N',COUNT,0)) AS ETC56-600N ,SUM(COUNT) AS TOTAL FRO
 `HAPSU`.`BARCODE_2D_TIME` GROUP BY TIME WITH ROLLUP |
+----------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------+
1 row in set (0.00 sec)

Answer 5

出错信息：mysql> PREPARE stmt2 FROM @QQ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to y
ear '-542N,SUM(IF(TYPE='ETC54-623N',COUNT,0)) AS ETC54-623N,SUM(IF(TYPE='ETC54-628N',' at line 1

Answer 6

SELECT ifnull(TIME,'total'),SUM(IF(TYPE='ETC56-542N',COUNT,0)) AS ETC56-542N,SUM(IF(TYPE='ETC54-623N',COUNT,0)) AS ETC54-623N,SUM(IF(TYPE='ETC54-62
N',COUNT,0)) AS ETC54-628N,SUM(IF(TYPE='ETC56-605N',COUNT,0)) AS ETC56-605N,SUM(IF(TYPE='ETC56-600N',COUNT,0)) AS ETC56-600N ,SUM(COUNT) AS TOTAL FRO
 `HAPSU`.`BARCODE_2D_TIME` GROUP BY TIME WITH ROLLUP
AS ETC56-542N这不是允许的命名，改为 AS `ETC56-542N`

Answer 7

SET @EE='';
SELECT @EE:=CONCAT(@EE,'SUM(IF(TYPE=\'',TYPE,'\'',',COUNT,0)) AS `',TYPE,'`,') FROM (SELECT DISTINCT TYPE FROM BARCODE_2D_TIME) A;
SET @QQ=CONCAT('SELECT ifnull(TIME,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(COUNT) AS TOTAL FROM BARCODE_2D_TIME GROUP BY TIME WITH ROLLUP');
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;