两个表的col2,col3字段并没有关系,只要把他们唯一的排进去就行,但是不要这种结果: col1 col2 col3 ---------------------- 1 a 1 e 1 b 1 f
declare @t1 table(col1 int,col2 varchar(10)) insert into @t1 select 1,'a' insert into @t1 select 1,'b' insert into @t1 select 2,'c' insert into @t1 select 3,'d'declare @t2 table(col1 int,col3 varchar(10)) insert into @t2 select 1,'e' insert into @t2 select 1,'f' insert into @t2 select 1,'g' insert into @t2 select 2,'h'select col1=isnull(a.col1,b.col1), col2=isnull(a.col2,''), col3=isnull(b.col3,'') from @t1 a full outer join @t2 b on a.col1=b.col1 and (select count(*) from @t1 where col1=a.col1 and col2<=a.col2)= (select count(*) from @t2 where col1=b.col1 and col3<=b.col3) order by col1/* col1 col2 col3 ----------- ---------- ---------- 1 a e 1 b f 1 g 2 c h 3 d */
if object_id('table1') is not null drop table table1 go select 1 as col1, 'a' as col2 into table1 union select 1, 'b' union select 2, 'c' union select 3, 'd'if object_id('table2') is not null drop table table2 go select 1 as col1, 'e' as col3 into table2 union select 1, 'f' union select 1, 'g' union select 2, 'h'select isnull(aa.col1, bb.col1) as col1, isnull(aa.col2, ''), isnull(bb.col3, '')5 from (select col1, col2, (select count(*) from table1 where col1 = a.col1 and col2 < a.col2) as findex from table1 a) aa full join (select col1, col3, (select count(*) from table2 where col1 = a.col1 and col3 < a.col3) as findex from table2 a)bb on aa.col1 = bb.col1 and aa.findex = bb.findex order by col1, isnull(aa.findex, bb.findex) /* 1 a e 1 b f 1 g 2 c h 3 d */drop table table1 drop table table2
if object_id('table1') is not null drop table table1 go select 1 as col1, 'a' as col2 into table1 union select 1, 'b' union select 2, 'c' union select 3, 'd'if object_id('table2') is not null drop table table2 go select 1 as col1, 'e' as col3 into table2 union select 1, 'f' union select 1, 'g' union select 2, 'h'select isnull(aa.col1, bb.col1) as col1, isnull(aa.col2, '') as col2, isnull(bb.col3, '') as col3 from (select col1, col2, (select count(*) from table1 where col1 = a.col1 and col2 < a.col2) as findex from table1 a) aa full join (select col1, col3, (select count(*) from table2 where col1 = a.col1 and col3 < a.col3) as findex from table2 a)bb on aa.col1 = bb.col1 and aa.findex = bb.findex order by col1, isnull(aa.findex, bb.findex) /* 1 a e 1 b f 1 g 2 c h 3 d */drop table table1 drop table table2
select a.col1,a.col2,id=(select count(1) from ta where col1=a.col1 and col2<=a.col2) into #1 from ta aselect a.col1,a.col3,id=(select count(1) from tb where col1=a.col1 and col3<=a.col3) into #2 from tb aselect col1=(case when a.col1 is null then b.col1 else a.col1 end), isnull(col2,''), isnull(col3,'') from #1 a full join #2 b on a.col1=b.col1 and a.id=b.iddrop table #1,#2
set nocount on create table ta(col1 int,col2 varchar(10)) insert into ta select 1,'a' insert into ta select 1,'b' insert into ta select 2,'c' insert into ta select 3,'d'create table tb(col1 int,col3 varchar(10)) insert into tb select 1,'e' insert into tb select 1,'f' insert into tb select 1,'g' insert into tb select 2,'h'select a.col1,a.col2,id=(select count(1) from ta where col1=a.col1 and col2<=a.col2) into #1 from ta aselect a.col1,a.col3,id=(select count(1) from tb where col1=a.col1 and col3<=a.col3) into #2 from tb aselect col1=(case when a.col1 is null then b.col1 else a.col1 end), isnull(col2,''), isnull(col3,'') from #1 a full join #2 b on a.col1=b.col1 and a.id=b.iddrop table #1,#2,ta,tb
set nocount on create table ta(col1 int,col2 varchar(10)) insert into ta select 1,'a' insert into ta select 1,'b' insert into ta select 2,'c' insert into ta select 3,'d'create table tb(col1 int,col3 varchar(10)) insert into tb select 1,'e' insert into tb select 1,'f' insert into tb select 1,'g' insert into tb select 2,'h'select a.col1,a.col2,id=(select count(1) from ta where col1=a.col1 and col2<=a.col2) into #1 from ta aselect a.col1,a.col3,id=(select count(1) from tb where col1=a.col1 and col3<=a.col3) into #2 from tb aselect col1=(case when a.col1 is null then b.col1 else a.col1 end), col2=isnull(col2,''), col3=isnull(col3,'') from #1 a full join #2 b on a.col1=b.col1 and a.id=b.iddrop table #1,#2,ta,tb
如果在加一个表table3 col1 col4 ------------- 1 k 2 z 需要结果如下 /* 1 a e k 1 b f 1 g 2 c h z 3 d */
SQL也继续添加就是了 if object_id('table1') is not null drop table table1 go select 1 as col1, 'a' as col2 into table1 union select 1, 'b' union select 2, 'c' union select 3, 'd'if object_id('table2') is not null drop table table2 go select 1 as col1, 'e' as col3 into table2 union select 1, 'f' union select 1, 'g' union select 2, 'h'if object_id('table3') is not null drop table table3 go select 1 as col1, 'k' as col4 into table3 union select 2, 'z'select isnull(isnull(aa.col1, bb.col1), cc.col1) as col1, isnull(aa.col2, '') as col2, isnull(bb.col3, '') as col3, isnull(cc.col4, '') as col4 from (select col1, col2, (select count(*) from table1 where col1 = a.col1 and col2 < a.col2) as findex from table1 a) aa full join (select col1, col3, (select count(*) from table2 where col1 = a.col1 and col3 < a.col3) as findex from table2 a) bb on aa.col1 = bb.col1 and aa.findex = bb.findex full join (select col1, col4, (select count(*) from table3 where col1 = a.col1 and col4 < a.col4) as findex from table3 a) cc on aa.col1 = cc.col1 and aa.findex = cc.findex order by col1, isnull(isnull(aa.findex, bb.findex), cc.findex) /* col1 col2 col3 col4 1 a e k 1 b f 1 g 2 c h z 3 d */drop table table1 drop table table2 drop table table3
--生成测试数据 declare @t1 table(col1 int,col2 varchar(10)) insert into @t1 select 1,'a' insert into @t1 select 1,'b' insert into @t1 select 2,'c' insert into @t1 select 3,'d'declare @t2 table(col1 int,col3 varchar(10)) insert into @t2 select 1,'e' insert into @t2 select 1,'f' insert into @t2 select 1,'g' insert into @t2 select 2,'h'declare @t3 table(col1 int,col4 varchar(10)) insert into @t3 select 1,'k' insert into @t3 select 2,'z' --执行查询处理 select col1=isnull(a.col1,b.col1), col2=isnull(a.col2,''), col3=isnull(b.col3,''), col4=isnull(c.col4,'') from @t1 a full outer join @t2 b on a.col1=b.col1 and (select count(*) from @t1 where col1=a.col1 and col2<=a.col2)= (select count(*) from @t2 where col1=b.col1 and col3<=b.col3) full outer join @t3 c on isnull(a.col1,b.col1)=c.col1 and isnull((select count(*) from @t1 where col1=a.col1 and col2<=a.col2),(select count(*) from @t2 where col1=b.col1 and col3<=b.col3))= (select count(*) from @t3 where col1=c.col1 and col4<=c.col4) order by col1/* col1 col2 col3 col4 ----------- ---------- ---------- ---------- 1 a e k 1 b f 1 g 2 c h z 3 d */
select isnull(isnull(aa.col1, bb.col1), cc.col1) as col1, isnull(aa.col2, '') as col2, isnull(bb.col3, '') as col3, isnull(cc.col4, '') as col4 from (select col1, col2, (select count(*) from table1 where col1 = a.col1 and col2 < a.col2) as findex from table1 a) aa full join (select col1, col3, (select count(*) from table2 where col1 = a.col1 and col3 < a.col3) as findex from table2 a) bb on aa.col1 = bb.col1 and aa.findex = bb.findex full join (select col1, col4, (select count(*) from table3 where col1 = a.col1 and col4 < a.col4) as findex from table3 a) cc on isnull(aa.col1, bb.col1) = cc.col1 and isnull(aa.findex, bb.findex) = cc.findex--这个地方改一下 order by col1, isnull(isnull(aa.findex, bb.findex), cc.findex)
col1 col2 col3
----------------------
1 a
1 e
1 b
1 f
insert into @t1 select 1,'a'
insert into @t1 select 1,'b'
insert into @t1 select 2,'c'
insert into @t1 select 3,'d'declare @t2 table(col1 int,col3 varchar(10))
insert into @t2 select 1,'e'
insert into @t2 select 1,'f'
insert into @t2 select 1,'g'
insert into @t2 select 2,'h'select
col1=isnull(a.col1,b.col1),
col2=isnull(a.col2,''),
col3=isnull(b.col3,'')
from
@t1 a
full outer join
@t2 b
on
a.col1=b.col1
and
(select count(*) from @t1 where col1=a.col1 and col2<=a.col2)=
(select count(*) from @t2 where col1=b.col1 and col3<=b.col3)
order by
col1/*
col1 col2 col3
----------- ---------- ----------
1 a e
1 b f
1 g
2 c h
3 d
*/
go
select 1 as col1, 'a' as col2
into table1
union select 1, 'b'
union select 2, 'c'
union select 3, 'd'if object_id('table2') is not null drop table table2
go
select 1 as col1, 'e' as col3
into table2
union select 1, 'f'
union select 1, 'g'
union select 2, 'h'select isnull(aa.col1, bb.col1) as col1, isnull(aa.col2, ''), isnull(bb.col3, '')5
from (select col1, col2, (select count(*) from table1 where col1 = a.col1 and col2 < a.col2) as findex from table1 a) aa
full join (select col1, col3, (select count(*) from table2 where col1 = a.col1 and col3 < a.col3) as findex from table2 a)bb
on aa.col1 = bb.col1 and aa.findex = bb.findex
order by col1, isnull(aa.findex, bb.findex)
/*
1 a e
1 b f
1 g
2 c h
3 d
*/drop table table1
drop table table2
go
select 1 as col1, 'a' as col2
into table1
union select 1, 'b'
union select 2, 'c'
union select 3, 'd'if object_id('table2') is not null drop table table2
go
select 1 as col1, 'e' as col3
into table2
union select 1, 'f'
union select 1, 'g'
union select 2, 'h'select isnull(aa.col1, bb.col1) as col1, isnull(aa.col2, '') as col2, isnull(bb.col3, '') as col3
from (select col1, col2, (select count(*) from table1 where col1 = a.col1 and col2 < a.col2) as findex from table1 a) aa
full join (select col1, col3, (select count(*) from table2 where col1 = a.col1 and col3 < a.col3) as findex from table2 a)bb
on aa.col1 = bb.col1 and aa.findex = bb.findex
order by col1, isnull(aa.findex, bb.findex)
/*
1 a e
1 b f
1 g
2 c h
3 d
*/drop table table1
drop table table2
into #1 from ta aselect a.col1,a.col3,id=(select count(1) from tb where col1=a.col1 and col3<=a.col3)
into #2 from tb aselect col1=(case when a.col1 is null then b.col1 else a.col1 end),
isnull(col2,''),
isnull(col3,'')
from #1 a full join #2 b on a.col1=b.col1 and a.id=b.iddrop table #1,#2
create table ta(col1 int,col2 varchar(10))
insert into ta select 1,'a'
insert into ta select 1,'b'
insert into ta select 2,'c'
insert into ta select 3,'d'create table tb(col1 int,col3 varchar(10))
insert into tb select 1,'e'
insert into tb select 1,'f'
insert into tb select 1,'g'
insert into tb select 2,'h'select a.col1,a.col2,id=(select count(1) from ta where col1=a.col1 and col2<=a.col2)
into #1 from ta aselect a.col1,a.col3,id=(select count(1) from tb where col1=a.col1 and col3<=a.col3)
into #2 from tb aselect col1=(case when a.col1 is null then b.col1 else a.col1 end),
isnull(col2,''),
isnull(col3,'')
from #1 a full join #2 b on a.col1=b.col1 and a.id=b.iddrop table #1,#2,ta,tb
create table ta(col1 int,col2 varchar(10))
insert into ta select 1,'a'
insert into ta select 1,'b'
insert into ta select 2,'c'
insert into ta select 3,'d'create table tb(col1 int,col3 varchar(10))
insert into tb select 1,'e'
insert into tb select 1,'f'
insert into tb select 1,'g'
insert into tb select 2,'h'select a.col1,a.col2,id=(select count(1) from ta where col1=a.col1 and col2<=a.col2)
into #1 from ta aselect a.col1,a.col3,id=(select count(1) from tb where col1=a.col1 and col3<=a.col3)
into #2 from tb aselect col1=(case when a.col1 is null then b.col1 else a.col1 end),
col2=isnull(col2,''),
col3=isnull(col3,'')
from #1 a full join #2 b on a.col1=b.col1 and a.id=b.iddrop table #1,#2,ta,tb
col1 col4
-------------
1 k
2 z
需要结果如下
/*
1 a e k
1 b f
1 g
2 c h z
3 d
*/
if object_id('table1') is not null drop table table1
go
select 1 as col1, 'a' as col2
into table1
union select 1, 'b'
union select 2, 'c'
union select 3, 'd'if object_id('table2') is not null drop table table2
go
select 1 as col1, 'e' as col3
into table2
union select 1, 'f'
union select 1, 'g'
union select 2, 'h'if object_id('table3') is not null drop table table3
go
select 1 as col1, 'k' as col4
into table3
union select 2, 'z'select isnull(isnull(aa.col1, bb.col1), cc.col1) as col1, isnull(aa.col2, '') as col2, isnull(bb.col3, '') as col3, isnull(cc.col4, '') as col4
from (select col1, col2, (select count(*) from table1 where col1 = a.col1 and col2 < a.col2) as findex from table1 a) aa
full join (select col1, col3, (select count(*) from table2 where col1 = a.col1 and col3 < a.col3) as findex from table2 a) bb
on aa.col1 = bb.col1 and aa.findex = bb.findex
full join (select col1, col4, (select count(*) from table3 where col1 = a.col1 and col4 < a.col4) as findex from table3 a) cc
on aa.col1 = cc.col1 and aa.findex = cc.findex
order by col1, isnull(isnull(aa.findex, bb.findex), cc.findex)
/*
col1 col2 col3 col4
1 a e k
1 b f
1 g
2 c h z
3 d
*/drop table table1
drop table table2
drop table table3
declare @t1 table(col1 int,col2 varchar(10))
insert into @t1 select 1,'a'
insert into @t1 select 1,'b'
insert into @t1 select 2,'c'
insert into @t1 select 3,'d'declare @t2 table(col1 int,col3 varchar(10))
insert into @t2 select 1,'e'
insert into @t2 select 1,'f'
insert into @t2 select 1,'g'
insert into @t2 select 2,'h'declare @t3 table(col1 int,col4 varchar(10))
insert into @t3 select 1,'k'
insert into @t3 select 2,'z'
--执行查询处理
select
col1=isnull(a.col1,b.col1),
col2=isnull(a.col2,''),
col3=isnull(b.col3,''),
col4=isnull(c.col4,'')
from
@t1 a
full outer join
@t2 b
on
a.col1=b.col1
and
(select count(*) from @t1 where col1=a.col1 and col2<=a.col2)=
(select count(*) from @t2 where col1=b.col1 and col3<=b.col3)
full outer join
@t3 c
on
isnull(a.col1,b.col1)=c.col1
and
isnull((select count(*) from @t1 where col1=a.col1 and col2<=a.col2),(select count(*) from @t2 where col1=b.col1 and col3<=b.col3))=
(select count(*) from @t3 where col1=c.col1 and col4<=c.col4)
order by
col1/*
col1 col2 col3 col4
----------- ---------- ---------- ----------
1 a e k
1 b f
1 g
2 c h z
3 d
*/
from (select col1, col2, (select count(*) from table1 where col1 = a.col1 and col2 < a.col2) as findex from table1 a) aa
full join (select col1, col3, (select count(*) from table2 where col1 = a.col1 and col3 < a.col3) as findex from table2 a) bb
on aa.col1 = bb.col1 and aa.findex = bb.findex
full join (select col1, col4, (select count(*) from table3 where col1 = a.col1 and col4 < a.col4) as findex from table3 a) cc
on isnull(aa.col1, bb.col1) = cc.col1 and isnull(aa.findex, bb.findex) = cc.findex--这个地方改一下
order by col1, isnull(isnull(aa.findex, bb.findex), cc.findex)