求助MYSQL交叉報(bào)表

海德

表結(jié)構(gòu)是
id xswd xsmc xsje xsje2 xsje3
序號 銷售網(wǎng)點(diǎn) 銷售產(chǎn)品名稱 金額 買單 獎勵
1 網(wǎng)點(diǎn)1 A 1 2 5
2 網(wǎng)點(diǎn)2 B 2 6 8
3 網(wǎng)點(diǎn)3 C 4 1 6
4 網(wǎng)點(diǎn)1 B 16 66 66
能不能取數(shù)變成這樣的交叉報(bào)表


A 買單 獎勵 B 買單 獎勵 C 買單 獎勵
網(wǎng)點(diǎn)1 1 2 5 16 66 66 0 0 0
網(wǎng)點(diǎn)2 2 6 8 0 0 0 0 0 0
網(wǎng)點(diǎn)3

網(wǎng)點(diǎn)金額 買單取合計(jì)數(shù)

我上網(wǎng)查了很多看到一個文章，但運(yùn)行出錯不知道錯在那里
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

你那數(shù)據(jù)格式不好看啊，有沒有空格什么的看不出來，數(shù)據(jù)或者使用代碼框框起來，或者使用附件提供。

另外，你的語句運(yùn)行出錯，報(bào)什么錯呢，要貼出來。

最后，這個就是普通的“列轉(zhuǎn)行”的問題，你可以搜索相關(guān)的內(nèi)容參考，實(shí)在不行的時候，把表結(jié)構(gòu)和數(shù)據(jù)附上，方便大家?guī)兔�測試。

huowz

有個報(bào)表工具，grid++report做交叉表很方便，http://video.sina.com.cn/v/b/112738927-2697789081.html這里有個視頻，前面是數(shù)據(jù)介紹，從5分45秒開始具體將怎么操作。

南瓜-學(xué)習(xí)者

如果只有3個網(wǎng)點(diǎn)，我覺得你可以先分組計(jì)算，然后在EXCEL中轉(zhuǎn)置，應(yīng)該可以達(dá)到這個效果。

南瓜-學(xué)習(xí)者

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;

南瓜-學(xué)習(xí)者

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;