分一下组就是了:
select isnull(a.学号,isnull(b.学号,isnull(c.学号,d.学号))) 学号,
语文=max(a.语文),数学=max(b.数学),英语=max(c.英语),物理=max(d.物理)
from #ta a full join #tb b on a.学号=b.学号
full join #tc c on a.学号=c.学号
full join #td d on a.学号=d.学号
group by isnull(a.学号,isnull(b.学号,isnull(c.学号,d.学号)))
select isnull(a.学号,isnull(b.学号,isnull(c.学号,d.学号))) 学号,
语文=max(a.语文),数学=max(b.数学),英语=max(c.英语),物理=max(d.物理)
from #ta a full join #tb b on a.学号=b.学号
full join #tc c on a.学号=c.学号
full join #td d on a.学号=d.学号
group by isnull(a.学号,isnull(b.学号,isnull(c.学号,d.学号)))
from #ta a, #tb b,#tc c,#td d where a.学号*=b.学号 and a.学号*=c.学号 and a.学号*=d.学号
insert into #ta select '001',57
insert into #ta select '002',86
insert into #ta select '003',76create table #tb(学号 varchar(10),数学 int)
insert into #tb select '002',80
insert into #tb select '003',66
insert into #tb select '004',90create table #tc(学号 varchar(10),英语 int)
insert into #tc select '003',88
insert into #tc select '004',62
insert into #tc select '005',77create table #td(学号 varchar(10),物理 int)
insert into #td select '004',82
insert into #td select '005',89
insert into #td select '001',83select isnull(a.学号,isnull(b.学号,isnull(c.学号,d.学号))) 学号,
语文=min(a.语文),数学=max(b.数学),英语=max(c.英语),物理=max(d.物理)
from #ta a full join #tb b on a.学号=b.学号
full join #tc c on a.学号=c.学号
full join #td d on a.学号=d.学号
group by isnull(a.学号,isnull(b.学号,isnull(c.学号,d.学号)))/*
学号 语文 数学 英语 物理
---------- ----------- ----------- ----------- -----------
001 57 NULL NULL 83
002 86 80 NULL NULL
003 76 66 88 NULL
004 NULL 90 62 82
005 NULL NULL 77 89
*/