mysql交叉表问题

littlebirds 2012-02-13 08:57:07
建立视图：


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


对此视图求交叉表：


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

请问错在哪里？
...全文
146 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
littlebirds 2012-02-13
  • 打赏
  • 举报
 回复
[Quote=引用 7 楼 acmain_chm 的回复:]

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……
[/Quote]

请问这个怎么改？我改了很久都没改对。
ACMAIN_CHM 2012-02-13
  • 打赏
  • 举报
 回复
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`
littlebirds 2012-02-13
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 acmain_chm 的回复:]

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……
[/Quote]

出错信息：


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
littlebirds 2012-02-13
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 acmain_chm 的回复:]

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……
[/Quote]
执行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)
littlebirds 2012-02-13
  • 打赏
  • 举报
 回复
前面三条语句执行都没问题，但是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造成的？
ACMAIN_CHM 2012-02-13
  • 打赏
  • 举报
 回复
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语句是什么样，应该是有语法错误。
littlebirds 2012-02-13
  • 打赏
  • 举报
 回复
以上数据是视图得到的结果。
littlebirds 2012-02-13
  • 打赏
  • 举报
 回复
测试数据：


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
littlebirds 2012-02-13
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 acmain_chm 的回复:]

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(@……
[/Quote]

错把``弄成了''，难怪半天没结果出来。

谢谢！
ACMAIN_CHM 2012-02-13
  • 打赏
  • 举报
 回复
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;
MySQL 管理利器 mysql-utilities
Re：MySQL 管理利器 mysql-utilities ===================================== # mysql-utilitiesa 概念 MySQL 作为一款非常流行的开源数据库，支持它的工具越来越多，对于使用者来讲，选择一款好的管理工具，对于...
在MySQL中实现交叉表查询2（动态交叉表）
在MySQL中实现交叉表查询2（动态交叉表） 交叉表分为静态交叉表和动态交叉表。其中静态交叉表中的列是固定的，因此相对容易实现；而动态交叉表中的列需要动态生成。 一、静态交叉表的实现 参见上一篇文章：在MySQL中实现交叉表查询1（静态交叉表） https://blog.csdn.net/weixin_44377973/article/details/103099573。 二、动态交叉表的实现 创...
交叉表 mysql_mysql交叉表查询解决方案整理
交叉表是一种常用的分类汇总查询。使用交叉表查询，可以显示表中某个字段的汇总值，并将它们分组，其中一组列在数据表的左侧，另一组列在数据表的上部。行和列的交叉处可以对数据进行多种汇总计算，如：求和、平均值、记数、最大值、最小值等。使用交叉表查询数据非常直观明了，被广泛应用。交叉表查询也是数据库的一个特点。以下例子收集自csdn整理数据样本：create table tx(id int primary ...
在MySQL中实现交叉表查询1（静态交叉表）
在MySQL中实现交叉表查询1（静态交叉表） 一、什么是交叉表 交叉表查询是将来源于某个表中的字段进行分组，一组列在交叉表左侧，一组列在交叉表上部，并在交叉表行与列交叉处显示表中某个字段的各种计算值。比如： create table stu( s_id int auto_increment, name char(20) not null default '', gender...
Mysql的交叉表查询
最近由于找工作，所以在恶补PHP和Mysql方面的东西，发现还是学习的不够。这几天看到关于mysql的交叉表查询的内容，根据自己的理解简单的尝试了下。由于MonsterHunter World较火，就拿怪物做个例子吧。首先建立张怪物信息表：CREATE TABLE `monster_info` (   `monster` varchar(16) DEFAULT NULL COMMENT '怪物名称...
MySQL

56,678

社区成员

56,708

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章