最近公司一SSRS報表需求改變,同比店鋪的選取為連續28天有銷售的店鋪,思考良久,發現可以利用ROW_NUMBER() OVER(PARTITION BY COLUMN01 ORDER BY COLUMN02)來實現,ROW_NUMBER()從1開始,按照COLUMN01分組以及COLUMN02排序.
1:先看數據源,要求取出上面連續28天內銷售金額大於0的XF_STORECODE :
2: 分解一下,由圖中看出,只要取XF_STORECODE,COUNT(1)>28就可以了.
3: 完整代碼:
4: 文本代碼
1 WITH STORE_COMP AS
2 (SELECT T.XF_STORECODE, T.XF_TXDATE, SUM(T.XF_AMTSOLD) XF_AMTSOLD
3 FROM XF_VIPITEMDM T, XF_ITEMMAS T1
4 WHERE T.XF_PLU = T1.XF_PLU
5 AND SUBSTR(T.XF_STORECODE, 3, 2) IN ('NK', 'SC', 'NS', 'MB')
6 AND T1.XF_GROUP2 = 'NIKE'
7 AND T1.XF_GROUP9 <> '特許裝備'
8 AND T.XF_TXDATE >= TO_DATE('20140501', 'yyyymmdd')
9 AND T.XF_TXDATE <= TO_DATE('20140531', 'yyyymmdd')
10 GROUP BY T.XF_STORECODE, T.XF_TXDATE
11 HAVING SUM(T.XF_AMTSOLD) > 0)
12 SELECT XF_STORECODE
13 FROM (SELECT XF_STORECODE,
14 XF_TXDATE,
15 ROW_NUMBER() OVER(PARTITION BY XF_STORECODE ORDER BY XF_TXDATE) ROW_NUM,
16 TRUNC(XF_TXDATE - ROW_NUMBER()
17 OVER(PARTITION BY XF_STORECODE ORDER BY XF_TXDATE)) ROWDIFF
18 FROM STORE_COMP)
19 GROUP BY XF_STORECODE, ROWDIFF
20 HAVING COUNT (1) >= 28
5: 如有更好的方法,歡迎賜教,謝謝!
6: 本人欲建一個運動行業的網站,歡迎有網頁制作技術的朋友利用業余時間一起合作. 有興趣的可以和我聯系QQ 568625626.
