求查询同一列中相同值的记录写法?

[asdw001]

表a的结构如下:
id t a1
1 ta 0
2 tc 4
3 tt 7
4 rt 7
5 gg 7
6 sr 2
7 qq 4
8 sd 5
9 vs 5
10 ws 4
11 rf 9

想把表a中a1列中相邻数据的相同值查询出来,其余的值清零.
并写入到b表b1列中,结构如下:
id t a1 b1
1 ta 0 0
2 tc 4 0
3 tt 7 7
4 rt 7 7
5 gg 7 7
6 sr 2 0
7 qq 4 0
8 sd 5 5
9 vs 5 5
10 ws 4 0
11 rf 9 0



/* 表a代码如下:
create table a(id int,t text,a1 int)
insert into a values(1,'ta',0)
insert into a values(2,'tc',4)
insert into a values(3,'tt',7)
insert into a values(4,'rt',7)
insert into a values(5,'gg',7)
insert into a values(6,'sr',2)
insert into a values(7,'qq',4)
insert into a values(8,'sd',5)
insert into a values(9,'vs',5)
insert into a values(10,'ws',4)
insert into a values(11,'rf',9)
go
*/

[pt1314917]

create table aa(id int,t text,aa1 int) 
insert into  aa  values(1,'taa',0) 
insert into  aa  values(2,'tc',4)         
insert into  aa  values(3,'tt',7) 
insert into  aa  values(4,'rt',7) 
insert into  aa  values(5,'gg',7) 
insert into  aa  values(6,'sr',2) 
insert into  aa  values(7,'qq',4) 
insert into  aa  values(8,'sd',5) 
insert into  aa  values(9,'vs',5) 
insert into  aa  values(10,'ws',4) 
insert into  aa  values(11,'rf',9) 

select t1.*,b1=(case when t1.aa1-t2.aa1=0 or t1.aa1-t3.aa1=0 then t1.aa1 else 0 end) from aa t1,aa t2,aa t3
where t1.id+1=t2.id and t1.id-1=t3.id

[asdw001]

表a的结构如下:
id      t      a1
1	ta	0
2	tc	4
3	tt	7
4	rt	7
5	gg	7
6	sr	2
7	qq	4
8	sd	5
9	vs	5
10	ws	4
11	rf	9

想把表a中a1列中相邻数据的相同值查询出来,其余的值清零.
并写入到b表b1列中,结构如下:
id      t      a1     b1
1	ta	0     0
2	tc	4     0
3	tt	7     7
4	rt	7     7
5	gg	7     7
6	sr	2     0
7	qq	4     0
8	sd	5     5
9	vs	5     5
10	ws	4     0
11	rf	9     0



/* 表a代码如下:
create table a(id int,t text,a1 int) 
insert into  a  values(1,'ta',0) 
insert into  a  values(2,'tc',4)         
insert into  a  values(3,'tt',7) 
insert into  a  values(4,'rt',7) 
insert into  a  values(5,'gg',7) 
insert into  a  values(6,'sr',2) 
insert into  a  values(7,'qq',4) 
insert into  a  values(8,'sd',5) 
insert into  a  values(9,'vs',5) 
insert into  a  values(10,'ws',4) 
insert into  a  values(11,'rf',9) 
go
*/

[leo_lesley]

create table a(id int,t text,a1 int)
insert into a values(1,'ta',0)
insert into a values(2,'tc',4)
insert into a values(3,'tt',7)
insert into a values(4,'rt',7)
insert into a values(5,'gg',7)
insert into a values(6,'sr',2)
insert into a values(7,'qq',4)
insert into a values(8,'sd',5)
insert into a values(9,'vs',5)
insert into a values(10,'ws',4)
insert into a values(11,'rf',9)
go


select *,b1=isnull((select top 1 a1 from a where (id=t.id+1 or id=t.id-1) and a1=t.a1),0) from a t




drop table a

[asdw001]

好快啊,老大,谢谢

[tim_spac]

create   table   a(id   int,t   text,a1   int)  
insert   into     a     values(1,'ta',0)  
insert   into     a     values(2,'tc',4)                  
insert   into     a     values(3,'tt',7)  
insert   into     a     values(4,'rt',7)  
insert   into     a     values(5,'gg',7)  
insert   into     a     values(6,'sr',2)  
insert   into     a     values(7,'qq',4)  
insert   into     a     values(8,'sd',5)  
insert   into     a     values(9,'vs',5)  
insert   into     a     values(10,'ws',4)  
insert   into     a     values(11,'rf',9)  
go 
-- 想把表a中a1列中相邻数据的相同值查询出来,其余的值清零.并写入到b表b1列中,

select a.id, t, a1, b1=case when b.id is null then 0 else a1 end
into b
from a
left join (
	select id 
	from a a0 
	where ( exists (select 1 from a a1 where a0.a1=a1.a1 and a0.id+1=a1.id) or
	 	    exists (select 1 from a a1 where a0.a1=a1.a1 and a0.id-1=a1.id) )
	) as b on a.id=b.id

select * from b

-- id,t,a1,b1
-- 1,ta,0,0
-- 2,tc,4,0
-- 3,tt,7,7
-- 4,rt,7,7
-- 5,gg,7,7
-- 6,sr,2,0
-- 7,qq,4,0
-- 8,sd,5,5
-- 9,vs,5,5
-- 10,ws,4,0
-- 11,rf,9,0
-- 
-- （所影响的行数为 11 行）
--