34,838
社区成员




create table cp(F2 varchar(5))
insert into cp(F2)
select 'AA' union all
select 'AA' union all
select 'AA' union all
select 'BB' union all
select 'BB' union all
select 'BB' union all
select 'BB'
select F2,
identity(int,1,1) 'rn'
into #T
from cp
select a.rn-b.minrn+1 'F1',a.F2
from #T a
inner join(select F2,
min(rn) 'minrn'
from #T
group by F2) b on a.F2=b.F2
/*
F1 F2
----------- -----
1 AA
2 AA
3 AA
1 BB
2 BB
3 BB
4 BB
(7 行受影响)
*/
-- 先设定每个分组的数量
SELECT *
INTO #T1
FROM (
SELECT 'AA' F2, 3 c UNION ALL
SELECT 'BB' F2, 4 c
) T
SELECT v.number F1,
#T1.F2
INTO #T
FROM #T1
JOIN master.dbo.spt_values v
ON v.type = 'p'
AND v.number > 0
AND v.number <= #T1.c
SELECT * FROM #T
F1 F2
----------- ----
1 AA
2 AA
3 AA
1 BB
2 BB
3 BB
4 BB
select row_number () over(partition by F2,order by F2) as rn ,* from t1