导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

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

asdw001 2007-12-21 10:11:51
表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
*/
...全文
144 点赞 收藏 5
写回复
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
tim_spac 2007-12-21

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 行)
--
回复
asdw001 2007-12-21
好快啊,老大,谢谢
回复
leo_lesley 2007-12-21
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 2007-12-21
表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 2007-12-21

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
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告