create table #a (tid int,行号 int) create table #b (mid int,分数 int) insert into #a values(1,1) insert into #a values(2,2) insert into #a values(3,2) insert into #a values(4,2) insert into #a values(5,3) insert into #a values(6,3)insert into #b values(1,2) insert into #b values(2,2) insert into #b values(3,1) insert into #b values(4,3) insert into #b values(5,4) insert into #b values(6,2) select a.行号,b.分数,c.平均分 from #a aleft join #b b on a.tid=b.mid left join (select a.行号,avg(b.分数) as '平均分' from #a a left join #b b on a.tid=b.mid group by a.行号) c on a.行号=c.行号 order by 1,2
create table #b (mid int,分数 int)
insert into #a values(1,1)
insert into #a values(2,2)
insert into #a values(3,2)
insert into #a values(4,2)
insert into #a values(5,3)
insert into #a values(6,3)insert into #b values(1,2)
insert into #b values(2,2)
insert into #b values(3,1)
insert into #b values(4,3)
insert into #b values(5,4)
insert into #b values(6,2)
select a.行号,b.分数,c.平均分 from #a aleft join #b b on a.tid=b.mid left join (select a.行号,avg(b.分数) as '平均分' from #a a
left join #b b on a.tid=b.mid
group by a.行号) c on a.行号=c.行号 order by 1,2