SQL如何依品號分別產生連續序號SN

t-sql

jslin0724 2018-01-26 15:21:36 ‧ 9818 瀏覽

分享至

各位先進好 ...
小弟有份資料,依下列語法寫出來資料格式SN會連續序號顯示(如:原資料顯示格式)...
但希望是可以依"品號"分別產生SN序號(如:希望資料顯示格式)

原SQL語法
SELECT ROW_NUMBER() OVER (ORDER BY (TD004)DESC)SN, TD004, ONE
FROM(
SELECT (TC001 '-' TC002 '-' TD003)ONE, TD004
FROM bueno002..PURTC B1
INNER JOIN bueno002..PURTD B2
ON TC001=TD001 AND TC002=TD002 AND TD038='2'
WHERE TC014=N'Y' AND TC003 BETWEEN '20170101'AND'20171231' AND (B2.TD200 <> '0' AND B2.TD201 <> '') AND B2.TD004 LIKE '2A%'
)TW
GROUP BY TD004, ONE --TD005,TD006,TD008,TD009,TD010,TD011
ORDER BY TD004 DESC

原資料顯示格式
SN TD004 ONE
1 2A740100000 M330-20170214002-0001
2 2A700000002 A330-20170223006-0001
3 2A700000002 A330-20170619005-0001
4 2A700000002 A330-20170718001-0001
5 2A680100000 A330-20170202002-0001
6 2A680100000 A330-20170221001-0001
7 2A670000000 A330-20170316004-0001
8 2A480300001 A331-20170106002-0001
9 2A480300001 A331-20170215020-0001
10 2A480300001 A331-20170321003-0001
11 2A420100001 A330-20170320007-0001
12 2A420100001 A330-20170704006-0001
13 2A380100003 A330-20170831001-0001
14 2A340100008 A330-20171025008-0001
15 2A340100008 A330-20171130006-0001

希望資料顯示格式
SN TD004 ONE
1 2A740100000 M330-20170214002-0001
1 2A700000002 A330-20170223006-0001
2 2A700000002 A330-20170619005-0001
3 2A700000002 A330-20170718001-0001
1 2A680100000 A330-20170202002-0001
2 2A680100000 A330-20170221001-0001
1 2A670000000 A330-20170316004-0001
1 2A480300001 A331-20170106002-0001
2 2A480300001 A331-20170215020-0001
3 2A480300001 A331-20170321003-0001
1 2A420100001 A330-20170320007-0001
2 2A420100001 A330-20170704006-0001
1 2A380100003 A330-20170831001-0001
1 2A340100008 A330-20171025008-0001
2 2A340100008 A330-20171130006-0001

一級屠豬士 iT邦新手 2 級 ‧ 2018-01-27 09:53:47 檢舉

擠成一堆.
發問也不把資料整理好. 你的TDXXX ,亂七八糟的,也捨不得顯示一下.
Table 結構,你的測試資料的 insert 語法也不顯示出來. 是要我們還要幫你生喔.
當然啦,這種毛病也不只你啦.這幾年大概只有一兩個會問的比較好.
看發問的嚴謹程度,就能看出寫程式的用心程度了.

jslin0724 iT邦新手 4 級 ‧ 2018-01-29 08:37:17 檢舉

非常感謝不吝惜指教 ~~ 下一次一定改進 !!

登入發表討論

直播研討會

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

直播中

1 個回答

神Q超人

iT邦研究生 5 級 ‧ 2018-01-26 19:03:42

最佳解答


SELECT ROW_NUMBER() OVER (PARTITION BY TD004 ORDER BY (TD004)DESC)SN, TD004, ONE
FROM(
SELECT (TC001 '-' TC002 '-' TD003)ONE, TD004
FROM bueno002..PURTC B1
INNER JOIN bueno002..PURTD B2
ON TC001=TD001 AND TC002=TD002 AND TD038='2'
WHERE TC014=N'Y' AND TC003 BETWEEN '20170101'AND'20171231' AND (B2.TD200 <> '0' AND B2.TD201 <> '') AND B2.TD004 LIKE '2A%'
)TW
GROUP BY TD004, ONE --TD005,TD006,TD008,TD009,TD010,TD011
ORDER BY TD004 DESC

會是這種方式嗎?
可以參考一下我之前記錄的筆記
https://ithelp.ithome.com.tw/articles/10190257