表A：id number distance month
1 1001 19 1
2 1002 29 1
3 1003 38 1
4 1004 47 1
5 1001 27 2
6 1002 38 2
7 1003 47 2
8 1004 56 2
表B: distince factor
20 1
30 2
40 3
50 4
60 5
想得到表C,其中cost=A.distince×B.factor,factor取值规则是A.distince小于B.distince且最接近的一个如id=1 A.distince=19 B.factor=1
表C; number cost month
1001 19 1
1002 58 1
1003 104 1
1004 188 1
1001 54 2
1002 114 2
1003 188 2
1004 280 2
------解决方案--------------------
try
select number,A.distance*
(select top 1 factor from B where B.distance> A.distance order by B.distance)
cost,month from A
------解决方案--------------------
select a.number,a.distance*(select top 1 factor from b where b.distince> a.distance order by b.distince-a.distance) as cost,month