MSSQL SELECT近一年進貨單價後作漲跌幅比較

sql 資料庫

匿名 2012-03-07 11:42:13 ‧ 5124 瀏覽

分享至

目前已有寫出列出近一年進貨單價(非每月都會進貨,程式碼如下)
顯示畫面如圖1,希望能加入與上一筆單價比較後的漲跌幅顯示畫面如圖2
請問各位大大該用什麼語法修改

SELECT 進貨單.品號 '品號' ,
ISNULL(CAST(MAX(CASE DATEPART(mm,進貨單.進貨日期) WHEN 1 THEN 進貨單.單價 END) AS VARCHAR),'') AS '01',
ISNULL(CAST(MAX(CASE DATEPART(mm,進貨單.進貨日期) WHEN 2 THEN 進貨單.單價 END) AS VARCHAR),'') AS '02',
ISNULL(CAST(MAX(CASE DATEPART(mm,進貨單.進貨日期) WHEN 3 THEN 進貨單.單價 END) AS VARCHAR),'') AS '03',
ISNULL(CAST(MAX(CASE DATEPART(mm,進貨單.進貨日期) WHEN 4 THEN 進貨單.單價 END) AS VARCHAR),'') AS '04',
ISNULL(CAST(MAX(CASE DATEPART(mm,進貨單.進貨日期) WHEN 5 THEN 進貨單.單價 END) AS VARCHAR),'') AS '05',
ISNULL(CAST(MAX(CASE DATEPART(mm,進貨單.進貨日期) WHEN 6 THEN 進貨單.單價 END) AS VARCHAR),'') AS '06',
ISNULL(CAST(MAX(CASE DATEPART(mm,進貨單.進貨日期) WHEN 7 THEN 進貨單.單價 END) AS VARCHAR),'') AS '07',
ISNULL(CAST(MAX(CASE DATEPART(mm,進貨單.進貨日期) WHEN 8 THEN 進貨單.單價 END) AS VARCHAR),'') AS '08',
ISNULL(CAST(MAX(CASE DATEPART(mm,進貨單.進貨日期) WHEN 9 THEN 進貨單.單價 END) AS VARCHAR),'') AS '09',
ISNULL(CAST(MAX(CASE DATEPART(mm,進貨單.進貨日期) WHEN 10 THEN 進貨單.單價 END) AS VARCHAR),'') AS '10',
ISNULL(CAST(MAX(CASE DATEPART(mm,進貨單.進貨日期) WHEN 11 THEN 進貨單.單價 END) AS VARCHAR),'') AS '11',
ISNULL(CAST(MAX(CASE DATEPART(mm,進貨單.進貨日期) WHEN 12 THEN 進貨單.單價 END) AS VARCHAR),'') AS '12'
FROM 進貨單
WHERE 進貨單.進貨日期 &lt; (CONVERT(char(8),DATEADD(d,-DAY(getdate())+1,getdate()),112))
      AND 進貨單.進貨日期 >= (CONVERT(char(8),DATEADD(MM,DATEDIFF(MM,0,DATEADD(MM,-12,getdate())),0),112))      
GROUP BY 進貨單.品號
ORDER BY 進貨單.品號

登入發表討論

直播研討會

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

直播中

2 個回答

kaowoei

iT邦研究生 4 級 ‧ 2012-03-22 15:02:27

最佳解答

若是MS SQL 2005 以後，可以試著參考PIVOT的語法。

回應
分享
檢舉

登入發表回應

Albert

iT邦高手 1 級 ‧ 2012-03-07 21:50:19

這要用三層 Sub Select 就好了

這是我們的統計表很像你要的

我們是全球最大開放原碼 ERP
Skype: Adempiere/Compiere
技術轉移顧問 Albert

你有更困難的題目嗎 ?

SELECT cc.ID,cc.YY,
cc.M01,
cc.M02,CASE NVL(cc.X01,0) WHEN 0 THEN NULL ELSE ROUND(cc.M02/cc.X01,2) END AS P02,
cc.M03,CASE NVL(cc.X02,0) WHEN 0 THEN NULL ELSE ROUND(cc.M03/cc.X02,2) END AS P03,
cc.M04,CASE NVL(cc.X03,0) WHEN 0 THEN NULL ELSE ROUND(cc.M04/cc.X03,2) END AS P04,
cc.M05,CASE NVL(cc.X04,0) WHEN 0 THEN NULL ELSE ROUND(cc.M05/cc.X04,2) END AS P05,
cc.M06,CASE NVL(cc.X05,0) WHEN 0 THEN NULL ELSE ROUND(cc.M06/cc.X05,2) END AS P06
FROM (
SELECT bb.ID, bb.YY,
bb.M01,bb.M02,bb.M03,bb.M04,bb.M05,bb.M06,bb.M07,bb.M08,bb.M09,bb.M10,bb.M11,bb.M12,
NVL(bb.M12,NVL(bb.M11,NVL(bb.M10,NVL(bb.M09,NVL(bb.M08,NVL(bb.M07,NVL(bb.M06,NVL(bb.M05,NVL(bb.M04,NVL(bb.M03,NVL(bb.M02,NVL(bb.M01,0)))))))))))) AS X12,
NVL(bb.M11,NVL(bb.M10,NVL(bb.M09,NVL(bb.M08,NVL(bb.M07,NVL(bb.M06,NVL(bb.M05,NVL(bb.M04,NVL(bb.M03,NVL(bb.M02,NVL(bb.M01,0))))))))))) AS X11,
NVL(bb.M10,NVL(bb.M09,NVL(bb.M08,NVL(bb.M07,NVL(bb.M06,NVL(bb.M05,NVL(bb.M04,NVL(bb.M03,NVL(bb.M02,NVL(bb.M01,0)))))))))) AS X10,
NVL(bb.M09,NVL(bb.M08,NVL(bb.M07,NVL(bb.M06,NVL(bb.M05,NVL(bb.M04,NVL(bb.M03,NVL(bb.M02,NVL(bb.M01,0))))))))) AS X09,
NVL(bb.M08,NVL(bb.M07,NVL(bb.M06,NVL(bb.M05,NVL(bb.M04,NVL(bb.M03,NVL(bb.M02,NVL(bb.M01,0)))))))) AS X09,
NVL(bb.M07,NVL(bb.M06,NVL(bb.M05,NVL(bb.M04,NVL(bb.M03,NVL(bb.M02,NVL(bb.M01,0))))))) AS X07,
NVL(bb.M06,NVL(bb.M05,NVL(bb.M04,NVL(bb.M03,NVL(bb.M02,NVL(bb.M01,0)))))) AS X06,
NVL(bb.M05,NVL(bb.M04,NVL(bb.M03,NVL(bb.M02,NVL(bb.M01,0))))) AS X05,
NVL(bb.M04,NVL(bb.M03,NVL(bb.M02,NVL(bb.M01,0)))) AS X04,
NVL(bb.M03,NVL(bb.M02,NVL(bb.M01,0))) AS X03,
NVL(bb.M02,NVL(bb.M01,0)) AS X02,
NVL(bb.M01,0) AS X01
FROM (
SELECT aa.ID, aa.YY,
SUM(CASE aa.MM WHEN '01' THEN aa.Qty ELSE NULL END) AS M01,