有表table,结构如下:
AUTOID AA BB
1 1 aa
2 1 bb
3 1 cc
4 1 dd
5 1 ee
6 1 ff
7 1 gg
8 1 hh
9 2 ii
10 2 jj
11 2 kk
12 2 ll
13 2 mm
14 3 nn
15 3 oo
现在需要把表中AA字段相等的值的行BB值更新为AUTOID最小的BB字段的值,且连续的行不超过3的倍数,超过的更新为第3*N+1的BB字段的值行,更新后,表table的值如下:
AUTOID AA BB
1 1 aa
2 1 aa
3 1 aa
4 1 dd
5 1 dd
6 1 dd
7 1 gg
8 1 gg
9 2 ii
10 2 ii
11 2 ii
12 2 ll
13 2 ll
14 3 nn
15 3 nn
请问SQL语句怎么写呢?
AUTOID AA BB
1 1 aa
2 1 bb
3 1 cc
4 1 dd
5 1 ee
6 1 ff
7 1 gg
8 1 hh
9 2 ii
10 2 jj
11 2 kk
12 2 ll
13 2 mm
14 3 nn
15 3 oo
现在需要把表中AA字段相等的值的行BB值更新为AUTOID最小的BB字段的值,且连续的行不超过3的倍数,超过的更新为第3*N+1的BB字段的值行,更新后,表table的值如下:
AUTOID AA BB
1 1 aa
2 1 aa
3 1 aa
4 1 dd
5 1 dd
6 1 dd
7 1 gg
8 1 gg
9 2 ii
10 2 ii
11 2 ii
12 2 ll
13 2 ll
14 3 nn
15 3 nn
请问SQL语句怎么写呢?
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([AUTOID] int,[AA] int,[BB] nvarchar(2))
Insert #T
select 1,1,N'aa' union all
select 2,1,N'bb' union all
select 3,1,N'cc' union all
select 4,1,N'dd' union all
select 5,1,N'ee' union all
select 6,1,N'ff' union all
select 7,1,N'gg' union all
select 8,1,N'hh' union all
select 9,2,N'ii' union all
select 10,2,N'jj' union all
select 11,2,N'kk' union all
select 12,2,N'll' union all
select 13,2,N'mm' union all
select 14,3,N'nn' union all
select 15,3,N'oo'
Go
;WITH a
as
(
Select
*,ROW_NUMBER()OVER(PARTITION BY AA,([AUTOID]-1)%3+1 ORDER BY [AUTOID]) AS row
from #T
)
UPDATE b
SET bb=c.bb
FROM a AS b
CROSS APPLY
(SELECT TOP 1 [BB] FROM a WHERE row=b.row AND AA=b.AA ORDER BY [AUTOID]) AS cGO
SELECT * FROM #T
/*
AUTOID AA BB
1 1 aa
2 1 aa
3 1 aa
4 1 dd
5 1 dd
6 1 dd
7 1 gg
8 1 gg
9 2 ii
10 2 ii
11 2 ii
12 2 ll
13 2 ll
14 3 nn
15 3 nn
*/
insert into tb select 1,1,'aa'
insert into tb select 2,1,'bb'
insert into tb select 3,1,'cc'
insert into tb select 4,1,'dd'
insert into tb select 5,1,'ee'
insert into tb select 6,1,'ff'
insert into tb select 7,1,'gg'
insert into tb select 8,1,'hh'
insert into tb select 9,2,'ii'
insert into tb select 10,2,'jj'
insert into tb select 11,2,'kk'
insert into tb select 12,2,'ll'
insert into tb select 13,2,'mm'
insert into tb select 14,3,'nn'
insert into tb select 15,3,'oo'
go
update b set BB=a.bb
from tb a inner join tb b on a.AA=b.AA and a.AUTOID<b.AUTOID and a.AUTOID/3=(b.AUTOID-1)/3
select * from tb
/*
AUTOID AA BB
----------- ----------- ----------
1 1 aa
2 1 aa
3 1 aa
4 1 cc
5 1 cc
6 1 cc
7 1 ff
8 1 ff
9 2 ii
10 2 ii
11 2 ii
12 2 ii
13 2 ll
14 3 nn
15 3 nn(15 行受影响)*/
go
drop table tb
and (AUTOID-1)%3=(a.AUTOID-1)%3 order by AUTOID)
from tb a
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([AUTOID] int,[AA] int,[BB] nvarchar(2))
Insert #T
select 1,1,N'aa' union all
select 2,1,N'bb' union all
select 3,1,N'cc' union all
select 4,1,N'dd' union all
select 5,1,N'ee' union all
select 6,1,N'ff' union all
select 7,1,N'gg' union all
select 8,1,N'hh' union all
select 9,2,N'ii' union all
select 10,2,N'jj' union all
select 11,2,N'kk' union all
select 12,2,N'll' union all
select 13,2,N'mm' union all
select 14,3,N'nn' union all
select 15,3,N'oo'
Go
;WITH a
as
(
Select
*,
(ROW_NUMBER()OVER(PARTITION BY [AA] ORDER BY [AUTOID])-1)%3+1 AS row
from #T
)
UPDATE b
SET bb=c.BB
FROM a AS b
INNER JOIN a AS c ON (c.[AUTOID]-c.row)=(b.AUTOID-b.row) AND b.AA=c.AA AND c.row=1goSELECT * FROM #T
/*
AUTOID AA BB
1 1 aa
2 1 aa
3 1 aa
4 1 dd
5 1 dd
6 1 dd
7 1 gg
8 1 gg
9 2 ii
10 2 ii
11 2 ii
12 2 ll
13 2 ll
14 3 nn
15 3 nn
*/
create table tb(AUTOID int,AA int,BB varchar(10))
insert into tb select 1,1,'aa'
insert into tb select 2,1,'bb'
insert into tb select 3,1,'cc'
insert into tb select 4,1,'dd'
insert into tb select 5,1,'ee'
insert into tb select 6,1,'ff'
insert into tb select 7,1,'gg'
insert into tb select 8,1,'hh'
insert into tb select 9,2,'ii'
insert into tb select 10,2,'jj'
insert into tb select 11,2,'kk'
insert into tb select 12,2,'ll'
insert into tb select 13,2,'mm'
insert into tb select 14,3,'nn'
insert into tb select 15,3,'oo'
go
update b set BB=a.bb
from tb a inner join tb b on a.AA=b.AA and a.AUTOID<b.AUTOID
inner join (select MIN(autoid)autoid,aa from tb group by AA)c
on c.AA=b.AA and (a.AUTOID-c.autoid)/3=(b.AUTOID-c.autoid)/3
select * from tb
/*
AUTOID AA BB
----------- ----------- ----------
1 1 aa
2 1 aa
3 1 aa
4 1 dd
5 1 dd
6 1 dd
7 1 gg
8 1 gg
9 2 ii
10 2 ii
11 2 ii
12 2 ll
13 2 ll
14 3 nn
15 3 nn(15 行受影响)*/
go
drop table tb