--sql 2000create table tb(name varchar(10), [group] int) insert into tb values('A' , 1) insert into tb values('B' , 2) insert into tb values('C' , 2) insert into tb values('D' , 2) insert into tb values('E' , 1) insert into tb values('F' , 1) insert into tb values('G' , 1) insert into tb values('H' , 2) goselect m.name name1, n.name name2 from (select * , px = (select count(1) from tb where [group] = 1 and name < t.name) + 1 from tb t where [group] = 1) m, (select * , px = (select count(1) from tb where [group] = 2 and name < t.name) + 1 from tb t where [group] = 2) n where m.px = n.px order by m.pxdrop table tb/* name1 name2 ---------- ---------- A B E C F D G H(所影响的行数为 4 行)*/
--sql 2005 create table tb(name varchar(10), [group] int) insert into tb values('A' , 1) insert into tb values('B' , 2) insert into tb values('C' , 2) insert into tb values('D' , 2) insert into tb values('E' , 1) insert into tb values('F' , 1) insert into tb values('G' , 1) insert into tb values('H' , 2) goselect m.name name1, n.name name2 from (select * , px = row_number() over(order by name) from tb where [group] = 1) m, (select * , px = row_number() over(order by name) from tb where [group] = 2) n where m.px = n.px order by m.pxdrop table tb/* name1 name2 ---------- ---------- A B E C F D G H(4 行受影响) */
可是发现一个问题,group各分组的成员人数都不一样多的时候,这个结果还是一样多的。 比如增加一个insert into tb values('I' , 2),I在查询结果中就没了。
declare @table table (name nvarchar(10),[group] int) insert into @table select 'A',1 union all select 'B',2 union all select 'C',2 union all select 'D',2 union all select 'E',1 union all select 'F',1 union all select 'G',1 union all select 'H',2 select a.name name1,b.name name2 from (select *,ROW_NUMBER()over(order by name) id from @table where [group]=1) a , (select *,ROW_NUMBER()over(order by name) id from @table where [group]=2) b where a.id=b.id name1 name2 ---------- ---------- A B E C F D G H(4 行受影响)
create table #test(name varchar(10), [group] int) insert into #test values('A' , 1) insert into #test values('B' , 2) insert into #test values('C' , 2) insert into #test values('D' , 2) insert into #test values('E' , 1) insert into #test values('F' , 1) insert into #test values('G' , 1) insert into #test values('H' , 2) insert into #test values('I' , 2) goselect m.name name1, n.name name2 from (select * , px = (select count(1) from #test where [group] = 1 and name < t.name) + 1 from #test t where [group] = 1) m, (select * , px = (select count(1) from #test where [group] = 2 and name < t.name) + 1 from #test t where [group] = 2) n where m.px = n.px order by m.px--drop table #testdrop table #t create table #t(name1 varchar(10), name2 varchar(10)) declare @name1 varchar(10) ,@name2 varchar(10)declare @i int ,@count intselect @count = max(groupcount) from ( select [group], count(*) groupcount from #test group by [group] ) T select @i =0 while @i<=@count begin set rowcount @i select @name1 = name from #test where [group] =1 select @name2 =name from #test where [group] =2
if EXISTS ( select name1 from #t where name1 = @name1) begin set @name1 = null endif EXISTS ( select name2 from #t where name1 = @name2) begin set @name2 = null end insert into #t select @name1,@name2 set @i =@i+1 endselect * from #t name1 name2 ---------- ---------- G I A B E C F D NULL H(5 行受影响)
drop table #testcreate table #test(name varchar(10), [group] int) insert into #test values('A' , 1) insert into #test values('B' , 2) insert into #test values('C' , 2) insert into #test values('D' , 2) insert into #test values('E' , 1) insert into #test values('F' , 1) insert into #test values('G' , 1) insert into #test values('H' , 2) insert into #test values('I' , 2) go drop table #t create table #t(name1 varchar(10), name2 varchar(10)) declare @name1 varchar(10) ,@name2 varchar(10)declare @i int ,@count intselect @count = max(groupcount) from ( select [group], count(*) groupcount from #test group by [group] ) T select @i =0 while @i<=@count begin set rowcount @i select @name1 = name from #test where [group] =1 select @name2 =name from #test where [group] =2
if EXISTS ( select name1 from #t where name1 = @name1) begin set @name1 = null endif EXISTS ( select name2 from #t where name1 = @name2) begin set @name2 = null end insert into #t select @name1,@name2 set @i =@i+1 endselect * from #t
select m.name name1,n.name name2 from (select * , px = (select count(1) from tb where [group] = 1 and name < t.name) + 1 from tb t where [group] = 1) m full join (select * , px = (select count(1) from tb where [group] = 2 and name < t.name) + 1 from tb t where [group] = 2) n on m.px = n.px order by m.px那就改成full join 吧
create table tb(name varchar(10), [group] int) insert into tb values('A' , 1) insert into tb values('B' , 2) insert into tb values('C' , 2) insert into tb values('D' , 2) insert into tb values('E' , 1) insert into tb values('F' , 1) insert into tb values('G' , 1) insert into tb values('H' , 2) insert into tb values('I' , 2) insert into tb values('J' , 2)goselect m.name name1, n.name name2 from (select * , px = row_number() over(order by name) from tb where [group] = 1) m full join (select * , px = row_number() over(order by name) from tb where [group] = 2) n on m.px = n.px order by m.px descdrop table tb
create table tb(name varchar(10), [group] int) insert into tb values('A' , 1) insert into tb values('B' , 2) insert into tb values('C' , 2) insert into tb values('D' , 2) insert into tb values('E' , 1) insert into tb values('F' , 1) insert into tb values('G' , 1) insert into tb values('H' , 2) insert into tb values('I' , 2) insert into tb values('J' , 2)goselect m.name name1, n.name name2 from (select * , px = row_number() over(order by name) from tb where [group] = 1) m full join (select * , px = row_number() over(order by name) from tb where [group] = 2) n on m.px = n.px order by m.px desc --select m.name name1,n.name name2 --from --(select * , px = (select count(1) from tb where [group] = 1 and name < t.name) + 1 from tb t where [group] = 1) m --full join --(select * , px = (select count(1) from tb where [group] = 2 and name < t.name) + 1 from tb t where [group] = 2) n --on m.px = n.px --order by m.px drop table tb
insert into tb values('A' , 1)
insert into tb values('B' , 2)
insert into tb values('C' , 2)
insert into tb values('D' , 2)
insert into tb values('E' , 1)
insert into tb values('F' , 1)
insert into tb values('G' , 1)
insert into tb values('H' , 2)
goselect m.name name1,
n.name name2
from
(select * , px = (select count(1) from tb where [group] = 1 and name < t.name) + 1 from tb t where [group] = 1) m,
(select * , px = (select count(1) from tb where [group] = 2 and name < t.name) + 1 from tb t where [group] = 2) n
where m.px = n.px order by m.pxdrop table tb/*
name1 name2
---------- ----------
A B
E C
F D
G H(所影响的行数为 4 行)*/
create table tb(name varchar(10), [group] int)
insert into tb values('A' , 1)
insert into tb values('B' , 2)
insert into tb values('C' , 2)
insert into tb values('D' , 2)
insert into tb values('E' , 1)
insert into tb values('F' , 1)
insert into tb values('G' , 1)
insert into tb values('H' , 2)
goselect m.name name1,
n.name name2
from
(select * , px = row_number() over(order by name) from tb where [group] = 1) m,
(select * , px = row_number() over(order by name) from tb where [group] = 2) n
where m.px = n.px order by m.pxdrop table tb/*
name1 name2
---------- ----------
A B
E C
F D
G H(4 行受影响)
*/
比如增加一个insert into tb values('I' , 2),I在查询结果中就没了。
declare @table table (name nvarchar(10),[group] int)
insert into @table select 'A',1
union all select 'B',2
union all select 'C',2
union all select 'D',2
union all select 'E',1
union all select 'F',1
union all select 'G',1
union all select 'H',2
select a.name name1,b.name name2 from
(select *,ROW_NUMBER()over(order by name) id from @table where [group]=1) a
,
(select *,ROW_NUMBER()over(order by name) id from @table where [group]=2) b
where a.id=b.id
name1 name2
---------- ----------
A B
E C
F D
G H(4 行受影响)
insert into #test values('A' , 1)
insert into #test values('B' , 2)
insert into #test values('C' , 2)
insert into #test values('D' , 2)
insert into #test values('E' , 1)
insert into #test values('F' , 1)
insert into #test values('G' , 1)
insert into #test values('H' , 2)
insert into #test values('I' , 2)
goselect m.name name1,
n.name name2
from
(select * , px = (select count(1) from #test where [group] = 1 and name < t.name) + 1 from #test t where [group] = 1) m,
(select * , px = (select count(1) from #test where [group] = 2 and name < t.name) + 1 from #test t where [group] = 2) n
where m.px = n.px order by m.px--drop table #testdrop table #t
create table #t(name1 varchar(10), name2 varchar(10))
declare @name1 varchar(10) ,@name2 varchar(10)declare @i int ,@count intselect @count = max(groupcount) from (
select [group], count(*) groupcount from #test group by [group]
) T
select @i =0
while @i<=@count
begin
set rowcount @i select @name1 = name from #test where [group] =1
select @name2 =name from #test where [group] =2
if EXISTS ( select name1 from #t where name1 = @name1)
begin
set @name1 = null
endif EXISTS ( select name2 from #t where name1 = @name2)
begin
set @name2 = null
end
insert into #t select @name1,@name2
set @i =@i+1
endselect * from #t
name1 name2
---------- ----------
G I
A B
E C
F D
NULL H(5 行受影响)
drop table #testcreate table #test(name varchar(10), [group] int)
insert into #test values('A' , 1)
insert into #test values('B' , 2)
insert into #test values('C' , 2)
insert into #test values('D' , 2)
insert into #test values('E' , 1)
insert into #test values('F' , 1)
insert into #test values('G' , 1)
insert into #test values('H' , 2)
insert into #test values('I' , 2)
go
drop table #t
create table #t(name1 varchar(10), name2 varchar(10))
declare @name1 varchar(10) ,@name2 varchar(10)declare @i int ,@count intselect @count = max(groupcount) from (
select [group], count(*) groupcount from #test group by [group]
) T
select @i =0
while @i<=@count
begin
set rowcount @i select @name1 = name from #test where [group] =1
select @name2 =name from #test where [group] =2
if EXISTS ( select name1 from #t where name1 = @name1)
begin
set @name1 = null
endif EXISTS ( select name2 from #t where name1 = @name2)
begin
set @name2 = null
end
insert into #t select @name1,@name2
set @i =@i+1
endselect * from #t
select m.name name1,n.name name2
from
(select * , px = (select count(1) from tb where [group] = 1 and name < t.name) + 1 from tb t where [group] = 1) m
full join
(select * , px = (select count(1) from tb where [group] = 2 and name < t.name) + 1 from tb t where [group] = 2) n
on m.px = n.px
order by m.px那就改成full join 吧
insert into tb values('A' , 1)
insert into tb values('B' , 2)
insert into tb values('C' , 2)
insert into tb values('D' , 2)
insert into tb values('E' , 1)
insert into tb values('F' , 1)
insert into tb values('G' , 1)
insert into tb values('H' , 2)
insert into tb values('I' , 2)
insert into tb values('J' , 2)goselect m.name name1,
n.name name2
from
(select * , px = row_number() over(order by name) from tb where [group] = 1) m
full join
(select * , px = row_number() over(order by name) from tb where [group] = 2) n
on m.px = n.px
order by m.px descdrop table tb
insert into tb values('A' , 1)
insert into tb values('B' , 2)
insert into tb values('C' , 2)
insert into tb values('D' , 2)
insert into tb values('E' , 1)
insert into tb values('F' , 1)
insert into tb values('G' , 1)
insert into tb values('H' , 2)
insert into tb values('I' , 2)
insert into tb values('J' , 2)goselect m.name name1,
n.name name2
from
(select * , px = row_number() over(order by name) from tb where [group] = 1) m
full join
(select * , px = row_number() over(order by name) from tb where [group] = 2) n
on m.px = n.px
order by m.px desc
--select m.name name1,n.name name2
--from
--(select * , px = (select count(1) from tb where [group] = 1 and name < t.name) + 1 from tb t where [group] = 1) m
--full join
--(select * , px = (select count(1) from tb where [group] = 2 and name < t.name) + 1 from tb t where [group] = 2) n
--on m.px = n.px
--order by m.px
drop table tb