表中数据:ID data1 data2
1 10 A
2 10 A
3 10 A
4 10 B
5 10 B
6 11 A
7 11 A
8 11 B
9 11 B需要查询出的结果如下filed1 filed2 filed3
1 10 A
2 10 A
3 10 A
1 10 B
2 10 B
1 11 A
2 11 A
1 11 B
2 11 B也就是要分别统计data1,data2,并在数据库的前面指出是同类数据中的第几个。
1 10 A
2 10 A
3 10 A
4 10 B
5 10 B
6 11 A
7 11 A
8 11 B
9 11 B需要查询出的结果如下filed1 filed2 filed3
1 10 A
2 10 A
3 10 A
1 10 B
2 10 B
1 11 A
2 11 A
1 11 B
2 11 B也就是要分别统计data1,data2,并在数据库的前面指出是同类数据中的第几个。
from table
filed1=row_number() over(partition by filed2,filed3 order by getdate())
filed2,
filed3
from
tb
set t.id=(select count(1) from tb where filed2=t.filed2 and filed1<=t.filed1)
from tb t
set t.filed1=(select count(1) from tb where filed2=t.filed2 and filed1<=t.filed1)
from tb t
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[data1] int,[data2] varchar(1))
insert [tb]
select 1,10,'A' union all
select 2,10,'A' union all
select 3,10,'A' union all
select 4,10,'B' union all
select 5,10,'B' union all
select 6,11,'A' union all
select 7,11,'A' union all
select 8,11,'B' union all
select 9,11,'B'
---查询---
select
filed1=row_number() over(partition by data1,data2 order by getdate()),
data1 as filed2,
data2 as filed3
from
tb
---结果---
filed1 filed2 filed3
-------------------- ----------- ------
1 10 A
2 10 A
3 10 A
1 10 B
2 10 B
1 11 A
2 11 A
1 11 B
2 11 B(9 行受影响)
select row_number() over(partition by data1,data2 order by data2) as 'filed1',data1 as 'filed2',data2 as 'filed3'
from table
if object_id('tb') is not null
drop table tb
go
create table tb(ID int,data1 int ,data2 varchar(2))
insert into tb
select 1, 10, 'A' union all
select 2, 10, 'A' union all
select 3, 10, 'A' union all
select 4, 10, 'B' union all
select 5, 10, 'B' union all
select 6, 11, 'A' union all
select 7, 11, 'A' union all
select 8, 11, 'B' union all
select 9, 11, 'B'
select filed1=ROW_NUMBER() over(partition by data1,data2 order by getdate() ),
data1 as filed2,data2 as filed3
from tb
filed1 filed2 filed3
1 10 A
2 10 A
3 10 A
1 10 B
2 10 B
1 11 A
2 11 A
1 11 B
2 11 B
'row_number' is not a recognized function name.
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[data1] int,[data2] varchar(1))
insert [tb]
select 1,10,'A' union all
select 2,10,'A' union all
select 3,10,'A' union all
select 4,10,'B' union all
select 5,10,'B' union all
select 6,11,'A' union all
select 7,11,'A' union all
select 8,11,'B' union all
select 9,11,'B'
---查询---
select
filed1=(select count(1)+1 from tb where data1=t.data1 and data2=t.data2 and id<t.id),
data1 as filed2,
data2 as filed3
from
tb t
---结果---
filed1 filed2 filed3
----------- ----------- ------
1 10 A
2 10 A
3 10 A
1 10 B
2 10 B
1 11 A
2 11 A
1 11 B
2 11 B(9 行受影响)