确定行数分组生成流水号
小弟我现在碰到一个问题。
我有如下数据
A01 A02 A03
A B
A C
A W
A E
A R
A A
B Q
A Y
我要实现A01分组生成流水号，并且每组最大为5行。
要求结果为：
A01 A02 A03
1 A B
1 A C
1 A W
1 A E
1 A R
2 A A
3 B Q
2 A Y 分组生成流水号
[解决办法]
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (A02 nvarchar(2),A03 nvarchar(2))
insert into [TB]
select 'A','B' union all
select 'A','C' union all
select 'A','W' union all
select 'A','E' union all
select 'A','R' union all
select 'A','A' union all
select 'B','Q' union all
select 'A','Y' union all
select 'A','C1' union all
select 'A','W1' union all
select 'A','E1' union all
select 'A','R1' union all
select 'A','A1' union all
select 'B','Q1' union all
select 'B','Q2' union all
select 'B','Q3' union all
select 'B','Q4' union all
select 'B','Q5' union all
select 'B','Q6'
select DENSE_RANK() OVER(ORDER BY A02,rn) A01,t.A02,t.A03
from (
select *,(ROW_NUMBER() OVER(PARTITION BY A02 ORDER BY A03) -1 )/5 rn
from TB
) t
select ntile(6) over(order by A02)as A01,*
from [TB]
