mysql交叉表有关问题

mysql交叉表问题

建立视图：

SQL code

  
create view barcode_2d_time as SELECT id,DATE_FORMAT(`date`,'%Y-%m-%d %H') as time,type,count(*) as count
FROM `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;