求助MYSQL交叉报表

海德

表结构是
id xswd xsmc xsje xsje2 xsje3
序号 销售网点 销售产品名称 金额 买单 奖励
1 网点1 A 1 2 5
2 网点2 B 2 6 8
3 网点3 C 4 1 6
4 网点1 B 16 66 66
能不能取数变成这样的交叉报表


A 买单 奖励 B 买单 奖励 C 买单 奖励
网点1 1 2 5 16 66 66 0 0 0
网点2 2 6 8 0 0 0 0 0 0
网点3

网点金额 买单取合计数

我上网查了很多看到一个文章，但运行出错不知道错在那里
SET @EE='';
SELECT @EE:=CONCAT(@EE,'SUM(IF(xsmc=/'',xsmc,'/'',',xsje,0)) AS ',xsmc,',') FROM (SELECT DISTINCT xsmc FROM jjxs) A;
SET @QQ=CONCAT('SELECT ifnull(xswd,/'total/'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(xsje) AS TOTAL FROM xsmc GROUP BY xswd WITH ROLLUP');
PREPARE stmt2 FROM @QQ;

seesea2517

你那数据格式不好看啊，有没有空格什么的看不出来，数据或者使用代码框框起来，或者使用附件提供。

另外，你的语句运行出错，报什么错呢，要贴出来。

最后，这个就是普通的“列转行”的问题，你可以搜索相关的内容参考，实在不行的时候，把表结构和数据附上，方便大家帮忙测试。

huowz

有个报表工具，grid++report做交叉表很方便，http://video.sina.com.cn/v/b/112738927-2697789081.html这里有个视频，前面是数据介绍，从5分45秒开始具体将怎么操作。

南瓜-学习者

如果只有3个网点，我觉得你可以先分组计算，然后在EXCEL中转置，应该可以达到这个效果。

南瓜-学习者

SELECT xsmd,sum(CASE WHEN xsmc='A' THEN xsje ELSE 0 END) Axsje,sum(CASE WHEN xsmc='A' THEN xsje1 ELSE 0 END) Axsje1,sum(CASE WHEN xsmc='A' THEN xsje2 ELSE 0 END) Axsje2,
sum(CASE WHEN xsmc='B' THEN xsje ELSE 0 END) Bxsje,sum(CASE WHEN xsmc='B' THEN xsje1 ELSE 0 END) Bxsje1,sum(CASE WHEN xsmc='B' THEN xsje2 ELSE 0 END) Bxsje2,
sum(CASE WHEN xsmc='C' THEN xsje ELSE 0 END) Cxsje,sum(CASE WHEN xsmc='C' THEN xsje1 ELSE 0 END) Cxsje1,sum(CASE WHEN xsmc='C' THEN xsje2 ELSE 0 END) Cxsje2
FROM
(SELECT xsmd,xsmc,SUM(xsje) xsje,SUM(xsje1) xsje1,SUM(xsje2) xsje2 FROM 表名  GROUP BY 1,2) cm GROUP BY 1;

南瓜-学习者

SELECT xsmd,sum(CASE WHEN xsmc='A' THEN xsje ELSE 0 END) Axsje,sum(CASE WHEN xsmc='A' THEN xsje1 ELSE 0 END) Axsje1,sum(CASE WHEN xsmc='A' THEN xsje2 ELSE 0 END) Axsje2,
sum(CASE WHEN xsmc='B' THEN xsje ELSE 0 END) Bxsje,sum(CASE WHEN xsmc='B' THEN xsje1 ELSE 0 END) Bxsje1,sum(CASE WHEN xsmc='B' THEN xsje2 ELSE 0 END) Bxsje2,
sum(CASE WHEN xsmc='C' THEN xsje ELSE 0 END) Cxsje,sum(CASE WHEN xsmc='C' THEN xsje1 ELSE 0 END) Cxsje1,sum(CASE WHEN xsmc='C' THEN xsje2 ELSE 0 END) Cxsje2
FROM test.yang_test1 GROUP BY 1;