例:表名TABLE里有列A,B,C (以C字段为小单位,把A列的值以倒序方式显示在B列上) A C
0001 种类1
0002 种类1
0005 种类1
0006 种类1
0001 种类2
0003 种类2
0004 种类2要求实现的效果为:
A C B
0001 种类1 0006
0002 种类1 0005
0005 种类1 0002
0006 种类1 0001
0001 种类2 0004
0003 种类2 0003
0004 种类2 0001
0001 种类1
0002 种类1
0005 种类1
0006 种类1
0001 种类2
0003 种类2
0004 种类2要求实现的效果为:
A C B
0001 种类1 0006
0002 种类1 0005
0005 种类1 0002
0006 种类1 0001
0001 种类2 0004
0003 种类2 0003
0004 种类2 0001
insert into tb select '0001','种类1'
insert into tb select '0002','种类1'
insert into tb select '0005','种类1'
insert into tb select '0006','种类1'
insert into tb select '0001','种类2'
insert into tb select '0003','种类2'
insert into tb select '0004','种类2'
go
select t1.A,t1.C,t2.B from (
select ROW_NUMBER() over(partition by C order by A)as rn,A,C from tb
)t1 inner join(
select ROW_NUMBER() over(partition by C order by A desc)as rn,A as B,C from tb
)t2 on t1.rn=t2.rn and t1.C=t2.C
go
drop table tb
/*
A C B
---------- ---------- ----------
0001 种类1 0006
0002 种类1 0005
0005 种类1 0002
0006 种类1 0001
0001 种类2 0004
0003 种类2 0003
0004 种类2 0001(7 行受影响)*/
create table tb(A nvarchar(10),C nvarchar(10), B nvarchar(10))
insert into tb select '0001','种类1',null
insert into tb select '0002','种类1',null
insert into tb select '0005','种类1',null
insert into tb select '0006','种类1',null
insert into tb select '0001','种类2',null
insert into tb select '0003','种类2',null
insert into tb select '0004','种类2',null
goupdate a set a.B = b.A
from (select id = row_number() over(partition by C order by A),B,C from tb) a
join (select id = row_number() over(partition by C order by A desc),A,C from tb) b
on a.id = b.id and a.C = b.Cselect * from tb
/*
A C B
---------- ---------- ----------
0001 种类1 0006
0002 种类1 0005
0005 种类1 0002
0006 种类1 0001
0001 种类2 0004
0003 种类2 0003
0004 种类2 0001(7 行受影响)
*/楼主,记得结贴哦
create table t1
(
a varchar(5),
c varchar(5)
)
insert into t1
select '0001', '种类1' union all
select '0002', '种类1' union all
select '0005', '种类1' union all
select '0006', '种类1' union all
select '0001', '种类2' union all
select '0003', '种类2' union all
select '0004', '种类2'
select a.a,a.c,b.a from
(select row_number() over(partition by c order by a) as ranking,* from t1) a,
(select row_number() over(partition by c order by a desc) as ranking,* from t1) b
where a.ranking=b.ranking and a.c=b.c
create table tb(A nvarchar(10),C nvarchar(10))
insert into tb select '0001','种类1'
insert into tb select '0002','种类1'
insert into tb select '0005','种类1'
insert into tb select '0006','种类1'
insert into tb select '0001','种类2'
insert into tb select '0003','种类2'
insert into tb select '0004','种类2'
go
select id=identity(int,1,1),* into #1 from tb order by C,A
select id=identity(int,1,1),* into #2 from tb order by C,A desc
select a.A,a.C,b.A as B from #1 a inner join #2 b on a.C=b.C and a.id=b.id
go
drop table tb,#1,#2
/*
A C B
---------- ---------- ----------
0001 种类1 0006
0002 种类1 0005
0005 种类1 0002
0006 种类1 0001
0001 种类2 0004
0003 种类2 0003
0004 种类2 0001(7 行受影响)
*/
drop table tb
create table tb
(
id int identity(1,1),
A nvarchar(30),
C nvarchar(30),
B nvarchar(50)
)
insert into tb values ('0001','种类1','')
insert into tb values ('0002','种类1','')
insert into tb values ('0005','种类1','')
insert into tb values ('0006','种类1','')
insert into tb values ('0004','种类1','')
insert into tb values ('0001','种类2','')
insert into tb values ('0003','种类2','')
insert into tb values ('0004','种类2','')update a set a.B=b.A
from
(select row_number() over(partition by C order by id) as rn ,B,C from tb) a
inner join
(select row_number() over(partition by C order by id desc) as rn ,A,C from tb) b on a.rn=b.rn and a.C=b.C
select * from tb