--错了,改: select 人员编号=isnull(a.人员编号,b.人员编号) ,T1项目编号=a.项目编号 ,T2项目编号=b.项目编号 from T1 a full join T2 b on a.人员编号=b.人员编号 and (select sum(1) from T1 where 人员编号=a.人员编号 and 项目编号<=a.项目编号) =(select sum(1) from T2 where 人员编号=b.人员编号 and 项目编号<=b.项目编号) order by 人员编号
--测试--测试数据 create table T1(人员编号 char(2),项目编号 varchar(10)) insert T1 select 'R1','A' union all select 'R1','B' union all select 'R2','C' union all select 'R2','D' union all select 'R2','E'create table T2(人员编号 char(2),项目编号 varchar(10)) insert T2 select 'R1','NA' union all select 'R1','NB' union all select 'R1','NC' union all select 'R2','ND' union all select 'R2','NE' go--查询 select 人员编号=isnull(a.人员编号,b.人员编号) ,T1项目编号=a.项目编号 ,T2项目编号=b.项目编号 from T1 a full join T2 b on a.人员编号=b.人员编号 and (select sum(1) from T1 where 人员编号=a.人员编号 and 项目编号<=a.项目编号) =(select sum(1) from T2 where 人员编号=b.人员编号 and 项目编号<=b.项目编号) order by 人员编号 go--删除测试 drop table t1,t2/*--测试结果 人员编号 T1项目编号 T2项目编号 ---- ---------- ---------- R1 A NA R1 B NB R1 NULL NC R2 C ND R2 D NE R2 E NULL(所影响的行数为 6 行) --*/
select t1.人员编号,t1.项目编号,t2.T2项目编号 ftom t1 full join t2 on t1.人员编号=t2.人员编号
zjcxc(邹建)不行啊,记录重复了,再帮忙看看,谢谢了
很不对,是我的错。。有人品问题的join我的错
create table #t1 (eid varchar(10),pid varchar(10)) create table #t2 (eid varchar(10),pid varchar(10)) insert #t1 select 'R1', 'A' union select 'R1', 'B' union select 'R2', 'C' union select 'R2', 'D' union select 'R2', 'E' insert #t2 select 'R1', 'NA' union select 'R1', 'NB' union select 'R1', 'NC' union select 'R2', 'ND' union select 'R2', 'NE'select isnull(b.eid,c.eid) eid,b.pid bp,c.pid cp from (select *,(select count(*) from #t1 where eid=a.eid and pid<=a.pid) as n from #t1 a) b full join (select *,(select count(*) from #t2 where eid=a.eid and pid<=a.pid) as n from #t2 a) c on b.eid=c.eid and b.n=c.n drop table #t1 drop table #t2
declare @t1 table(人员编号 char(2),项目编号 char(1)) declare @t2 table(人员编号 char(2),项目编号 char(2))insert @t1 select 'R1', 'A' union all select 'R1', 'B' union all select 'R2', 'C' union all select 'R2', 'D' union all select 'R2', 'E' insert @t2 select 'R1', 'NA' union all select 'R1', 'NB' union all select 'R1', 'NC' union all select 'R2', 'ND' union all select 'R2', 'NE'select isnull(a.人员编号,b.人员编号)人员编号 , a.项目编号, b.项目编号 from @t1 a full join @t2 b on a.人员编号 = b.人员编号 and (select count(*) from @t1 where a.人员编号 = 人员编号 and a.项目编号 >= 项目编号) = (select count(*) from @t2 where b.人员编号 = 人员编号 and b.项目编号 >= 项目编号) order by 1/*人员编号 项目编号 项目编号 ---- ---- ---- R1 A NA R1 B NB R1 NULL NC R2 E NULL R2 C ND R2 D NE(所影响的行数为 6 行) */
select 人员编号=isnull(a.人员编号,b.人员编号)
,T1项目编号=a.项目编号
,T2项目编号=b.项目编号
from T1 a
full join T2 b on a.人员编号=b.人员编号
and (select sum(1) from T1 where 人员编号=a.人员编号 and 项目编号<=a.项目编号)
=(select sum(1) from T2 where 人员编号=b.人员编号 and 项目编号<=b.项目编号)
order by 人员编号
create table T1(人员编号 char(2),项目编号 varchar(10))
insert T1 select 'R1','A'
union all select 'R1','B'
union all select 'R2','C'
union all select 'R2','D'
union all select 'R2','E'create table T2(人员编号 char(2),项目编号 varchar(10))
insert T2 select 'R1','NA'
union all select 'R1','NB'
union all select 'R1','NC'
union all select 'R2','ND'
union all select 'R2','NE'
go--查询
select 人员编号=isnull(a.人员编号,b.人员编号)
,T1项目编号=a.项目编号
,T2项目编号=b.项目编号
from T1 a
full join T2 b on a.人员编号=b.人员编号
and (select sum(1) from T1 where 人员编号=a.人员编号 and 项目编号<=a.项目编号)
=(select sum(1) from T2 where 人员编号=b.人员编号 and 项目编号<=b.项目编号)
order by 人员编号
go--删除测试
drop table t1,t2/*--测试结果
人员编号 T1项目编号 T2项目编号
---- ---------- ----------
R1 A NA
R1 B NB
R1 NULL NC
R2 C ND
R2 D NE
R2 E NULL(所影响的行数为 6 行)
--*/
ftom t1 full join t2 on t1.人员编号=t2.人员编号
create table #t2 (eid varchar(10),pid varchar(10))
insert #t1
select 'R1', 'A'
union select 'R1', 'B'
union select 'R2', 'C'
union select 'R2', 'D'
union select 'R2', 'E'
insert #t2
select 'R1', 'NA'
union select 'R1', 'NB'
union select 'R1', 'NC'
union select 'R2', 'ND'
union select 'R2', 'NE'select isnull(b.eid,c.eid) eid,b.pid bp,c.pid cp
from
(select *,(select count(*) from #t1 where eid=a.eid and pid<=a.pid) as n
from #t1 a) b full join
(select *,(select count(*) from #t2 where eid=a.eid and pid<=a.pid) as n
from #t2 a) c
on b.eid=c.eid and b.n=c.n drop table #t1
drop table #t2
declare @t2 table(人员编号 char(2),项目编号 char(2))insert @t1
select
'R1', 'A'
union all select
'R1', 'B'
union all select
'R2', 'C'
union all select
'R2', 'D'
union all select
'R2', 'E'
insert @t2
select
'R1', 'NA'
union all select
'R1', 'NB'
union all select
'R1', 'NC'
union all select
'R2', 'ND'
union all select
'R2', 'NE'select isnull(a.人员编号,b.人员编号)人员编号 ,
a.项目编号,
b.项目编号
from @t1 a full join @t2 b
on a.人员编号 = b.人员编号 and
(select count(*) from @t1 where a.人员编号 = 人员编号 and a.项目编号 >= 项目编号)
=
(select count(*) from @t2 where b.人员编号 = 人员编号 and b.项目编号 >= 项目编号)
order by 1/*人员编号 项目编号 项目编号
---- ---- ----
R1 A NA
R1 B NB
R1 NULL NC
R2 E NULL
R2 C ND
R2 D NE(所影响的行数为 6 行)
*/