现在表中记录格式如下:date salemoney categoryname
20110323 100.00 服饰类
20110323 50.00 家具类
20110324 200.00 服饰类
20110324 150.00 家具类
20110324 200.00 服饰类如果想查询得到如下结果,请问mysql中语句怎么写(类别是动态的,可能不止这两类,日期也是动态的)?结果:
20110323 20110324
服饰类 100.00 400.00
家具类 50.00 150.00
20110323 100.00 服饰类
20110323 50.00 家具类
20110324 200.00 服饰类
20110324 150.00 家具类
20110324 200.00 服饰类如果想查询得到如下结果,请问mysql中语句怎么写(类别是动态的,可能不止这两类,日期也是动态的)?结果:
20110323 20110324
服饰类 100.00 400.00
家具类 50.00 150.00
http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html
中我的回答
你好,我看了一下你在帖子中的答复,不过那个@EE和@QQ是怎么声明的呢?我是MYSQL菜鸟,以前一直用SQL SERVER
BEGIN
declare @EE varchar(2000) character set utf8;
declare @QQ varchar(2000) character set utf8;
SET @EE='';
SELECT @EE:=CONCAT(@EE,'SUM(IF(date=\'',date,'\'',',orderamount,0)) AS ',date,',') FROM
(SELECT distinct date from tab_dailyreport where date >= begindate and date <= enddate ) A;
SET @QQ=CONCAT('SELECT ifnull(categoryname,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(orderamount) AS TOTAL FROM ( select categoryname,date,sum( cast(orderamount as decimal(18,2)) ) as orderamount from tab_dailyreport where date >= begindate and date <= enddate
group by categoryname,date)x GROUP BY categoryname WITH ROLLUP');
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2; END;
我这么创建存储 过程,老是报错,好象错误是报的声明@EE,@QQ错误