sql語法問題

sql

gavinnokia 2010-11-02 12:08:49 ‧ 5242 瀏覽

分享至

title,date,pcs
1,25,10
2,26,20
3,27,30
上列是db中的結果.
如何用 sql 中的case或是其它語法改出如下的結果嗎?

title,25,26,27,sum
1,10,0,0,0,10
2,0,20,0,0,20
3,0,0,30,30
感謝幫忙.

外獅佬 iT邦大師 1 級 ‧ 2010-11-03 00:19:43 檢舉

MySQL並沒有PIVOT的語法，必須以subquery的方式處理...
請參考http://lists.mysql.com/mysql/216011

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

aqr199

iT邦新手 2 級 ‧ 2010-11-03 08:58:12

最佳解答

&lt;pre class="c" name="code">
select title, 
sum(case when date='25' then pcs else 0 end) as 25,
sum(case when date='26' then pcs else 0 end) as 26,
sum(case when date='27' then pcs else 0 end) as 27,
sum(pcs) as sum_pcs
from tablename
group by title

回應
分享
檢舉

登入發表回應

myhoney731

iT邦新手 3 級 ‧ 2010-11-02 12:53:09

SELECT * FROM
(
SELECT *,sum(PCS)As Sum1
FROM testTable group By title,date,pcs
) AS X
PIVOT(
sum(PCS)
FOR date in
([25],[26],[27])
) AS PVT
group by [title],[25],[26],[27],[sum1]

回應 1
分享
檢舉

gavinnokia iT邦研究生 3 級 ‧ 2010-11-02 14:04:38 檢舉

sorry,我是用在mysql.為什麼會出現錯誤呢?
是不是PIVOT只可以用在mssql. 還是我用在mysql要注意那裡.謝謝你的協助.
[Err] 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 'PIVOT(
sum(vvtest.pcs)
FOR vvtest.dateqq in
([25],[26],[27])
) AS PVT

登入發表回應