把co1 中b行对应的字符填到co1行中
create table Test
(testID int identity not null,
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 ')
如何把作查询得到以下结果：(把co1 中b行对应的字符填到co1行中
查询中不能出现 'm ', 'u '
co1 co2
m xyz
m m
m n
m y
u lpg
u u
u f
[解决办法]
规则没看懂,请详速.
[解决办法]
5alpgu
6buu
7bfu
那这个U什么来的?
------解决方案--------------------
select a.testid,a.co1,a.co2 ,
(select top 1 b.co2 from Test b where b.co1= 'b '
and not exists(select 1 from Test c where c.co1= 'a ' and c.testid <=a.testid and c.testid > b.testid) ) as aa
from Test a
--result
1axyzm
2bmm
3bnm
4bym
5alpgu
6buu
7bfu
[解决办法]
如果要update 表 的话
update Test
set co1=(select top 1 b.co2 from Test b where b.co1= 'b '
and not exists(select 1 from Test c where c.co1= 'a ' and c.testid <=a.testid and c.testid > b.testid) )
from Test a
1mxyz
2mm
3mn
4my
5ulpg
6uu
7uf
