declare @txb1 table(num int)insert into @txb1
select 10 union all
select 20 union all
select 30 union all
select 40 union all
select 50 union all
select 60 union all
select 70 union all
select 80select * from
(select num from @txb1 where num%20=10) a left join
(select num from @txb1 where num%20=0) b on a.num=b.num-10 left join
(select num from @txb1 where (num-20)%20=10) c on b.num=c.num-10
--结果
(所影响的行数为 8 行)num num num
----------- ----------- -----------
10 20 30
30 40 50
50 60 70
70 80 NULL(所影响的行数为 4 行)
select 10 union all
select 20 union all
select 30 union all
select 40 union all
select 50 union all
select 60 union all
select 70 union all
select 80select * from
(select num from @txb1 where num%20=10) a left join
(select num from @txb1 where num%20=0) b on a.num=b.num-10 left join
(select num from @txb1 where (num-20)%20=10) c on b.num=c.num-10
--结果
(所影响的行数为 8 行)num num num
----------- ----------- -----------
10 20 30
30 40 50
50 60 70
70 80 NULL(所影响的行数为 4 行)
insert into @txb1
select 10 union all
select 20 union all
select 30 union all
select 40 union all
select 50 union all
select 60 union all
select 70 union all
select 80
--这样逻辑更清楚点
select * from
(select num from @txb1 where num%20=10) a left join
(select num num1 from @txb1 where (num-10)%20=10) b on a.num=b.num1-10 left join
(select num num2 from @txb1 where (num-20)%20=10) c on b.num1=c.num2-10
(select num as num from @txb1 where num%20=10) a left join
(select num as num1 from @txb1 where num%20=0) b on a.num=b.num1-10 left join
(select num as num2 from @txb1 where (num-20)%20=10) c on b.num1=c.num2-10
所以
你们的答案太理想话了!!!
并没有达到我的目的!!!
如果是这样子,该怎么做呢?
create table txb1(num int)insert into txb1
select 10 union all
select 12 union all
select 16 union all
select 17 union all
select 30 union all
select 34 union all
select 40union all
select 50
最终的显示结果是:
num num1,num2
10 12 16
16 17 30
30 34 40
40 50 NULL
select 10 union all
select 12 union all
select 16 union all
select 17 union all
select 30 union all
select 34 union all
select 40union all
select 50select * from
(select num from @txb1 a where (select count(1) from @txb1 where num<=a.num)%2=1) a left join
(select num from @txb1 a where ((select count(1) from @txb1 where num<=a.num)-1)%2=1) b
on (select count(1) from @txb1 where num<=a.num)=(select count(1) from @txb1 where num<=b.num)-1 left join
(select num from @txb1 a where ((select count(1) from @txb1 where num<=a.num)-2)%2=1) c
on (select count(1) from @txb1 where num<=b.num)=(select count(1) from @txb1 where num<=c.num)-1
--结果
(所影响的行数为 8 行)num num num
----------- ----------- -----------
10 12 16
16 17 30
30 34 40
40 50 NULL(所影响的行数为 4 行)