--建立测试环境
Create Table #table1(xid int,xNum varchar(10))
--插入数据
insert into #table1
select 1 ,'A' union all
select 2,'C' union all
select 2,'B' union all
select 3,'C' union all
select 3,'D' union all
select 4, '0'
--建立测试环境
Create Table #table2(xid int,xNum2 varchar(10))
--插入数据
insert into #table2
select 2,'H' union all
select 3,'F' union all
select 1 , 'I' union all
select 1, 'K' /****
Result:
xID xNum xNum2
1 A I
1 Nul K
2 C H
2 B Null
3 C F
3 D Null
4 o Null
****/select id = identity(int,1,1),xid,xnum into #table11 from #table1
select id = identity(int,1,1),xid,xnum2 into #table21 from #table2---这地方：
select a.xid,a.xnum,b.xnum2 from #table11 a left join #table21 b
on a.xid=b.xid and (select count(*) from #table11 where xid=a.xid and id<a.id)=(select count(*) from #table21 where xid=b.xid and id<b.id)union select a.xid,b.xnum,a.xnum2 from #table21 a left outer join #table11 b
on a.xid=b.xid and (select count(*) from #table21 where xid=a.xid and id<a.id)=(select count(*) from #table11 where xid = b.xid and id<b.id)----上面的没问题：但是用right join的话
select b.xid,a.xnum,b.xnum2 from #table11 a right outer join #table21 b
on a.xid=b.xid and (select count(*) from #table21 where id< b.id )>0
便会出现
ODBC: 消息 0，级别 19，状态 1
SqlDumpExceptionHandler: 进程 230 发生了严重的异常 c0000005 EXCEPTION_ACCESS_VIOLATION。SQL Server 将终止该进程。错误，请问为什么会这样呢？
on a.xid=b.xid and (select count(*) from #table21 where xid=a.xid and id<a.id)=(select count(*) from #table11 where xid = b.xid and id<b.id)和select b.xid,a.xnum,b.xnum2 from #table11 a right outer join #table21 b
on a.xid=b.xid and (select count(*) from #table11 where xid=a.xid and id<a.id)=(select count(*) from #table21 where xid=b.xid and id<b.id)本来应该是一样的语句，但是下一个出错