表结构:
a b c
10 one two
11 three four
12 five six
14 seven eight
15 nignt ten
28 eleven twelve现要前把a字段中大于a字段的最小的值就相差要大于1以上的值同表关联起来
比如
14比12大且大于1所以符合条件
11比10大但是只大1所以不符合条件
最后的结果应为:
a_1 b_1 c_1 a_2 b_2 c_2
12 five six 14 seven eight
15 night ten 18 eleven twelve不知各位怎么实现,请指教。
a b c
10 one two
11 three four
12 five six
14 seven eight
15 nignt ten
28 eleven twelve现要前把a字段中大于a字段的最小的值就相差要大于1以上的值同表关联起来
比如
14比12大且大于1所以符合条件
11比10大但是只大1所以不符合条件
最后的结果应为:
a_1 b_1 c_1 a_2 b_2 c_2
12 five six 14 seven eight
15 night ten 18 eleven twelve不知各位怎么实现,请指教。
create table #temp1(a int,b char(10),c char(10))
insert into #temp1
select 10 , 'one' , 'two'
union
select 11, 'three' , 'four'
union
select 12 , 'five', 'six'
union
select 14 , 'seven', 'eight'
union
select 15 ,'nignt', 'ten'
union
select 28 ,'eleven', 'twelve'
insert into t1
select 10 , 'one' , 'two'
union
select 11, 'three' , 'four'
union
select 12 , 'five', 'six'
union
select 14 , 'seven', 'eight'
union
select 15 ,'nignt', 'ten'
union
select 28 ,'eleven', 'twelve'
----------------------------------------
select
aa.a as a_1,
aa.b as b_1,
aa.c as c_1,
b.a as a_2,
b.b as b_2,
b.c as c_3
from
(
select
* ,
(select top 1 b.a from t1 b where b.a >a.a) as aa
from T1 a
) aa
join
t1 b
on aa.aa = b.a
group by
aa.a,
aa.b,
aa.c,
aa.aa,
b.a,
b.b,
b.c
having (aa.aa - aa.a)>1--------------------结果
a b c
----------- ---------- ----------
10 one two
11 three four
12 five six
14 seven eight
15 nignt ten
28 eleven twelve a_1 b_1 c_1 a_2 b_2 c_3
----------- ---------- ---------- ----------- ---------- ----------
12 five six 14 seven eight
15 nignt ten 28 eleven twelve
a.b as b_1,
a.c as c1,
b.a_2 as a_2,
b.b_2 as b_2,
b.c_2 as c_2 from #temp1 a inner join
(
select a as a_2,b as b_2,c as c_2,(select max(a) as a from #temp1 where a<m.a) as a_1 from #temp1 m where (select m.a-max(a) as a from #temp1 where a<m.a)>1
)b
on a.a = b.a_1
a.b as b_1,
a.c as c_1,
b.a_2 as a_2,
b.b_2 as b_2,
b.c_2 as c_2 from #temp1 a inner join
(
select a as a_2,b as b_2,c as c_2,(select max(a) as a from #temp1 where a<m.a) as a_1 from #temp1 m where (select m.a-max(a) as a from #temp1 where a<m.a)>1
)b
on a.a = b.a_1 支持这个!!
a.b as b_1,
a.c as c1,
b.a_2 as a_2,
b.b_2 as b_2,
b.c_2 as c_2 from #temp1 a inner join
(
select a as a_2,b as b_2,c as c_2,(select max(a) as a from #temp1 where a<m.a) as a_1 from #temp1 m
)b
on a.a = b.a_1
where (a_2-a_1)>1----------把条件放到外面
insert into @temp1
select 10 , 'one' , 'two'
union
select 11, 'three' , 'four'
union
select 12 , 'five', 'six'
union
select 14 , 'seven', 'eight'
union
select 15 ,'nignt', 'ten'
union
select 28 ,'eleven', 'twelve'
select a.*,b.*
from @temp1 a,@temp1 b
where a.a<b.a-1 and
not exists(select 1 from @temp1 where a>a.a and a<b.a)