SQL怎么將相同資料的進行編號，遇到不相同的重新從1開始，效果如下。
44-9712769 1
44-9712767 1
44-9712767 2
44-9712769 1 因與上一條不相同，所以重新從1開始編號
44-9712769 2
44-9713423 1
44-9713423 2
44-9713423 3
44-9713423 4
44-9771549 1
uj5u.com熱心網友回復：
--測驗資料
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([col] nvarchar(30))
Insert #T
select N'44-9712769' union all
select N'44-9712767' union all
select N'44-9712767' union all
select N'44-9712769' union all
select N'44-9712769' union all
select N'44-9713423' union all
select N'44-9713423' union all
select N'44-9713423' union all
select N'44-9713423' union all
select N'44-9771549'
Go
--測驗資料結束
Select *,ROW_NUMBER()OVER(PARTITION BY col ORDER BY col) rn from #T
uj5u.com熱心網友回復：
use Tempdb
go
--> --> 中國風(Roy)生成測試數據
if not object_id(N'Tempdb..#tab') is null
drop table #tab
Go
Create table #tab([col] nvarchar(30))
Insert #tab
select N'44-9712769' union all
select N'44-9712767' union all
select N'44-9712767' union all
select N'44-9712769' union all
select N'44-9712769' union all
select N'44-9713423' union all
select N'44-9713423' union all
select N'44-9713423' union all
select N'44-9713423' union all
select N'44-9771549'
GO
;WITH cte AS (
Select *,ROW_NUMBER() OVER(PARTITION BY GETDATE() ORDER BY GETDATE()) AS rn
from #tab
),cte2 AS (
SELECT col,rn,rn-ROW_NUMBER() OVER(PARTITION BY col ORDER BY rn) AS rn2
FROM cte
)
SELECT col,ROW_NUMBER() OVER(PARTITION BY col,rn2 ORDER BY rn) AS rn3
FROM cte2
ORDER BY rn
uj5u.com熱心網友回復：
44-9712769 144-9712767 1
44-9712767 2
44-9712769 1 因與上一條不相同，所以重新從1開始編號
44-9712769 2
44-9713423 1
44-9713423 2
44-9713423 3
44-9713423 4
44-9771549 1
樓上回復的跟我要的結果有差異，44-9712769在下面第二次出現時要重新從1開始
uj5u.com熱心網友回復：
看我的，悟空忽略了你的細節
uj5u.com熱心網友回復：
對，看2樓雨停的吧，我那個有點問題uj5u.com熱心網友回復：
我再給你提供一個吧，把后面 from dual去掉應該可以直接在sqlserver執行。我本地無環境就不試了with tmp as(
select 1 as rn, '44-9712769' id from dual union all
select 2 as rn, '44-9712767' id from dual union all
select 3 as rn, '44-9712767' id from dual union all
select 4 as rn, '44-9712769' id from dual union all
select 5 as rn, '44-9712769' id from dual union all
select 6 as rn, '44-9713423' id from dual union all
select 7 as rn, '44-9713423' id from dual union all
select 8 as rn, '44-9713423' id from dual union all
select 9 as rn, '44-9713423' id from dual union all
select 10 as rn, '44-9771549' id from dual )
select rn, id, row_number() over(partition by rn1 - rn2 order by rn1 - rn2) as p_rn
from (select tmp.*,
row_number() over(order by rn) rn1,
row_number() over(partition by id order by rn) rn2
from tmp);
uj5u.com熱心網友回復：
我也嘗試寫了下， 寫著寫著就把自己繞進去了。還是得多看多學啊。腦筋不好使。
uj5u.com熱心網友回復：
感謝大哥的指導
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/155059.html
標籤：疑難問題
上一篇：幫忙！SQL中的列 'email' 的型別不能用作索引中的鍵列
下一篇：推薦軟體工程考研院校