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

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

asdw001 2007-12-21 10:11:51

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

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

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

3.2w+

MS-SQL Server相关内容讨论专区