mysql交叉表问题
建立视图：

SQL code

create view barcode_2d_time as SELECT id,DATE_FORMAT(`date`,'%Y-%m-%d %H') as time,type,count(*) as countFROM `barcode_2d`WHERE date >= '2012-02-08 08:00:00'AND date < '2012-02-09 08:00:00'GROUP BY TYPE , DATE_FORMAT(`date`,'%Y-%m-%d %H')ORDER BY DATE_FORMAT(`date`,'%Y-%m-%d %H') ASC

对此视图求交叉表：

SQL code

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;

但是结果报错：
#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-628N',COUNT,0)) AS ETC54-628N,SUM(IF(TYPE='ETC54-623N',' at line 1

请问错在哪里？

[解决办法]
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`
[解决办法]
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;