T1
city score
C1 90
C2 80
C3 50T2
province score
P1 70
P2 80T3
city province
C1 P1
C2 P2
C3 P2希望得到各个city的分数和对应省份的分数
Result:
T4
city averageScore
C1 80
C2 80
C3 65
city score
C1 90
C2 80
C3 50T2
province score
P1 70
P2 80T3
city province
C1 P1
C2 P2
C3 P2希望得到各个city的分数和对应省份的分数
Result:
T4
city averageScore
C1 80
C2 80
C3 65
select a.city,(a.score+isnull(c.score,0))/2
from t1 a
left join
t3 b
on a.city=b.city
left join t2 c
on b.province=c.province
select a.city,(isnull(a.score,0)+isnull(c.score,0))/2
from t1 a
left join t3 b
on a.city=b.city
left join t2 c
on b.province=c.province