再讨论如何把上面的a行对应的数据更新到b行?
create table Test
(Co1 varchar(5),
co2 varchar(10)
)
GO
insert into test (Co1,co2) values( 'a ' , 'xyz ')
insert into test (Co1,co2) values( 'b ' , 'm ')
insert into test (Co1,co2) values( 'b ' , 'n ')
insert into test (Co1,co2) values( 'b ' , 'y ')
insert into test (Co1,co2) values( 'a ' , 'lpg ')
insert into test (Co1,co2)values( 'b ' , 'u ')
insert into test (Co1,co2) Values( 'b ' , 'f ')
---如何把上面的a行对应的数据更新到b行?
--- wgzaaa (95)、的方法:
declare @A varchar(10)
set @A= ' '
update test set co1=(case when co1= 'a ' then co1 else @A end),
@A=(case when co1= 'a ' then co2 else @A end)
go
select * from test
a xyz
xyz m
xyz n
xyz y
a lpg
lpg u
lpg f
如果数据改一改
delete from Test
go
insert into test (Co1,co2) values( 'b ' , 'm ')
insert into test (Co1,co2) values( 'b ' , 'n ')
insert into test (Co1,co2) values( 'b ' , 'y ')
insert into test (Co1,co2) values( 'a ' , 'lpg ')
insert into test (Co1,co2)values( 'b ' , 'u ')
insert into test (Co1,co2) Values( 'b ' , 'f ')
如何得到？
可以加一列identity,最好是select 出来的别的方法。
xyz m
xyz n
xyz y
a lpg
lpg u
lpg f
[解决办法]
请问,当数据如下:
bm
bn
by
alpg
bu
bf
楼主希望得到怎样的结果?
[解决办法]
select id=identity(int),* into #1 from test
select [co1]=case when co1= 'b ' then
(select top 1 co2 from #1 where co1= 'a ' and id <a.id order by id desc)
else co1 end,co2 from #1 a
[解决办法]
他要的是update吧，select 可以不用 case,
select ta.*,(select top 1 b from @a tb where tb.c <ta.c and tb.a = 'a ' and ta.a <> 'a ' order by tb.c-ta.c desc) from @a ta
