select identity(int,1,1) as idd into # from tb select a.id as id1,b.id as id2 from (select * from # where idd%2=1) a left join (select * from # where idd%2=0) b on a.idd=b.idd-1
-- ========================================= -- -----------t_mac 小编------------------- --------------------希望有天成为大虾---- -- =========================================IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb(id int) go insert tb SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 0 select identity(int,1,1) as idd,* into #2 from tb select a.id as id1,b.id as id2 from (select * from #2 where idd%2=1) a left join (select * from #2 where idd%2=0) b on a.idd=b.idd-1goid1 id2 ----------- ----------- 1 2 3 4 5 6 7 8 9 0
select min(id) as id1,max(id) as id2 from ( select id,row_number() over (order by id) as id1 from [table] ) as t group by (id1+1)/2
2005的方法 -- ========================================= -- -----------t_mac 小编------------------- --------------------希望有天成为大虾---- -- =========================================IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb(id int) go insert tb SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 0 select a.id as id1,b.id as id2 from (select ROW_NUMBER() over (order by getdate()) AS idd,* from tb ) a left join (select ROW_NUMBER() over (order by getdate()) AS idd,* from tb ) b on a.idd=b.idd-1 where a.idd%2=1 and b.idd%2=0 goid1 id2 ----------- ----------- 1 2 3 4 5 6 7 8 9 0
declare @t table(id int) insert @t SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 0 select * from (select * from @t where id%2=0)a, (select * from @t where id%2=1)b where a.id=b.id-1
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb(id int) go insert tb SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 0 select t1.id,isnull(t2.id,0) id2 from tb t1 left join tb t2 on t1.id+1=t2.id where t1.id%2<>0 /* id id2 ----------- ----------- 1 2 3 4 5 6 7 8 9 0(5 行受影响) */
---------------------------------------------------------------- -- Author :fredrickhu(小F 向高手学习) -- Date :2009-08-10 13:48:28 ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int) insert [tb] select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 0 --------------开始查询--------------------------select * from (select * from tb where id%2=0)a, (select * from tb where id%2=1)b where a.id=b.id-1----------------结果---------------------------- /* id id ----------- ----------- 0 1 2 3 4 5 6 7 8 9(所影响的行数为 5 行) */
declare @tb table(id int) insert @tb select 1 insert @tb select 2 insert @tb select 3 insert @tb select 4 insert @tb select 5 insert @tb select 6 insert @tb select 7 insert @tb select 8 insert @tb select 9 insert @tb select 0 --select * from @tb select px=identity(int,1,1),id into #temp from @tb -- select * from #temp -- drop table #temp select id1=a.id ,id2=b.id from (select * from #temp where px%2=1) a , (select * from #temp where px%2=0) b where a.px=b.px-1drop table #temp /* id1 id2 ----------- ----------- 1 2 3 4 5 6 7 8 9 0(所影响的行数为 5 行)*/
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb(id int) go insert tb SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 0 select id1=case when id%2=1 then id end, id2 from tb join (select id2=case when id%2=0 then id end from tb where id%2=0 )t on id+1=id2 or id-9=id2 where id%2=1 id1 id2 ----------- ----------- 1 2 3 4 5 6 7 8 9 0(5 行受影响)
declare @t table(id int) insert @t SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 0 select idd=identity(int,1,1),* into #1 from @t where id%2=1 select idd=identity(int,1,1),* into #2 from @t where id%2=0 select t1.id,t2.id from #1 t1, #2 t2 where t1.idd=t2.idd id id ----------- ----------- 1 2 3 4 5 6 7 8 9 0 --select * from @t where id%2=0select * from (select * from @t where id%2=0)a, (select * from @t where id%2=1)b where a.id=b.id-1
select a.id as id1,b.id as id2
from (select * from # where idd%2=1) a
left join (select * from # where idd%2=0) b on a.idd=b.idd-1
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id int)
go
insert tb SELECT
1 UNION ALL SELECT
2 UNION ALL SELECT
3 UNION ALL SELECT
4 UNION ALL SELECT
5 UNION ALL SELECT
6 UNION ALL SELECT
7 UNION ALL SELECT
8 UNION ALL SELECT
9 UNION ALL SELECT
0
select identity(int,1,1) as idd,* into #2 from tb
select a.id as id1,b.id as id2
from (select * from #2 where idd%2=1) a
left join (select * from #2 where idd%2=0) b on a.idd=b.idd-1goid1 id2
----------- -----------
1 2
3 4
5 6
7 8
9 0
from (
select id,row_number() over (order by id) as id1 from [table]
) as t
group by (id1+1)/2
-- =========================================
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id int)
go
insert tb SELECT
1 UNION ALL SELECT
2 UNION ALL SELECT
3 UNION ALL SELECT
4 UNION ALL SELECT
5 UNION ALL SELECT
6 UNION ALL SELECT
7 UNION ALL SELECT
8 UNION ALL SELECT
9 UNION ALL SELECT
0
select a.id as id1,b.id as id2
from (select ROW_NUMBER() over (order by getdate()) AS idd,* from tb ) a
left join (select ROW_NUMBER() over (order by getdate()) AS idd,* from tb ) b on a.idd=b.idd-1
where a.idd%2=1 and b.idd%2=0
goid1 id2
----------- -----------
1 2
3 4
5 6
7 8
9 0
insert @t SELECT
1 UNION ALL SELECT
2 UNION ALL SELECT
3 UNION ALL SELECT
4 UNION ALL SELECT
5 UNION ALL SELECT
6 UNION ALL SELECT
7 UNION ALL SELECT
8 UNION ALL SELECT
9 UNION ALL SELECT
0 select * from
(select * from @t where id%2=0)a,
(select * from @t where id%2=1)b
where a.id=b.id-1
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id int)
go
insert tb SELECT
1 UNION ALL SELECT
2 UNION ALL SELECT
3 UNION ALL SELECT
4 UNION ALL SELECT
5 UNION ALL SELECT
6 UNION ALL SELECT
7 UNION ALL SELECT
8 UNION ALL SELECT
9 UNION ALL SELECT
0
select t1.id,isnull(t2.id,0) id2
from tb t1 left join tb t2 on t1.id+1=t2.id where t1.id%2<>0
/*
id id2
----------- -----------
1 2
3 4
5 6
7 8
9 0(5 行受影响)
*/
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-10 13:48:28
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int)
insert [tb]
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 0
--------------开始查询--------------------------select * from
(select * from tb where id%2=0)a,
(select * from tb where id%2=1)b
where a.id=b.id-1----------------结果----------------------------
/*
id id
----------- -----------
0 1
2 3
4 5
6 7
8 9(所影响的行数为 5 行)
*/
insert @tb select 1
insert @tb select 2
insert @tb select 3
insert @tb select 4
insert @tb select 5
insert @tb select 6
insert @tb select 7
insert @tb select 8
insert @tb select 9
insert @tb select 0
--select * from @tb
select px=identity(int,1,1),id into #temp from @tb
-- select * from #temp
-- drop table #temp
select id1=a.id ,id2=b.id
from
(select * from #temp where px%2=1) a ,
(select * from #temp where px%2=0) b where a.px=b.px-1drop table #temp
/*
id1 id2
----------- -----------
1 2
3 4
5 6
7 8
9 0(所影响的行数为 5 行)*/
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id int)
go
insert tb SELECT
1 UNION ALL SELECT
2 UNION ALL SELECT
3 UNION ALL SELECT
4 UNION ALL SELECT
5 UNION ALL SELECT
6 UNION ALL SELECT
7 UNION ALL SELECT
8 UNION ALL SELECT
9 UNION ALL SELECT
0
select id1=case when id%2=1 then id end, id2 from tb
join
(select id2=case when id%2=0 then id end from tb where id%2=0
)t
on id+1=id2 or id-9=id2
where id%2=1 id1 id2
----------- -----------
1 2
3 4
5 6
7 8
9 0(5 行受影响)
insert @t SELECT
1 UNION ALL SELECT
2 UNION ALL SELECT
3 UNION ALL SELECT
4 UNION ALL SELECT
5 UNION ALL SELECT
6 UNION ALL SELECT
7 UNION ALL SELECT
8 UNION ALL SELECT
9 UNION ALL SELECT
0
select idd=identity(int,1,1),* into #1 from @t where id%2=1
select idd=identity(int,1,1),* into #2 from @t where id%2=0
select t1.id,t2.id from
#1 t1,
#2 t2
where t1.idd=t2.idd
id id
----------- -----------
1 2
3 4
5 6
7 8
9 0
--select * from @t where id%2=0select * from
(select * from @t where id%2=0)a,
(select * from @t where id%2=1)b
where a.id=b.id-1