如果将一组数据如:
列1,列2,列3,
a 123 1
a 33 2
a ee 3
a g 4
b 32 1
b w 2
b wer 3
c w 1
c 33 2
d 32 1
d 12 2
d 33 3
d 11 4
求第三列按第一列相同时,流水号排序
列1,列2,列3,
a 123 1
a 33 2
a ee 3
a g 4
b 32 1
b w 2
b wer 3
c w 1
c 33 2
d 32 1
d 12 2
d 33 3
d 11 4
求第三列按第一列相同时,流水号排序
select *
from tb
order by col1,col3
select 列1,列2,列3=row_number() over(partition by 列1 order by getdate()) from tb
if object_id('tb','u') is not null
drop table tb
go
create table tb
(
列1 varchar(10),
列2 varchar(10)
)
go
insert into tb
select 'a','123' union all
select 'a','33' union all
select 'a','33' union all
select 'a','g' union all
select 'b','32' union all
select 'b','w' union all
select 'b','wer' union all
select 'c','w' union all
select 'c','33' union all
select 'd','32' union all
select 'd','12' union all
select 'd','33' union all
select 'd','11'
go
select 列1,列2,列3=row_number() over(partition by 列1 order by getdate()) from tb
go
/*
列1 列2 列3
---------- ---------- --------------------
a 123 1
a 33 2
a 33 3
a g 4
b 32 1
b w 2
b wer 3
c w 1
c 33 2
d 32 1
d 12 2
d 33 3
d 11 4(13 行受影响)
*/