iT邦幫忙

0

sql語法問題

sql

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

2 個回答

10
aqr199
iT邦新手 2 級 ‧ 2010-11-03 08:58:12
最佳解答
<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
6
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]

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

我要發表回答

立即登入回答