求一条联合查询语句

Question

表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

Answer 1

try
select number,A.distance*
(select top 1 factor from B where B.distance>A.distance order by B.distance)
cost,month from A

Answer 2

测试通过
number      cost        month       
----------- ----------- ----------- 
1001        19          1
1002        58          1
1003        114         1
1004        188         1
1001        54          2
1002        114         2
1003        188         2
1004        280         2（所影响的行数为 8 行）

Answer 3

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
from a--result 
1001     19      1
1002     58      1
1003     114     1
1004     188     1
1001     54      2
1002     114     2
1003     188     2
1004     280     2 
楼主的结果有一个错误 

Answer 4

create table #a([id] int ,number varchar(20), distance int, [month] varchar(20))
insert into #a
select    1 , '1001',      '19'      , '1'  union all
select    2 , '1002',      '29'      , '1' union all
select    3 , '1003',      '38'      , '1' union all
select    4 , '1004',      '47'      , '1' union all
select    5 , '1001',      '27'      , '2' union all
select    6 , '1002',      '38'      , '2' union all
select    7 , '1003',      '47'      , '2' union all
select    8 , '1004',      '56'      , '2'create table #b(distance int, factor varchar(20))
insert into #b
select       '20 ',           '1'union all
select       '30'  ,          '2'union all
select       '40'   ,         '3'union all
select       '50'    ,        '4'union all
select       '60'     ,       '5'
select *,distance*(select top 1 factor from #b where distance-a.distance>0 order by distance)  from #a a ---------------------------
id          number               distance    month                            
----------- -------------------- ----------- -------------------- ----------- 
1           1001                 19          1                    19
2           1002                 29          1                    58
3           1003                 38          1                    114
4           1004                 47          1                    188
5           1001                 27          2                    54
6           1002                 38          2                    114
7           1003                 47          2                    188
8           1004                 56          2                    280（所影响的行数为 8 行）

Answer 5

b.distince-a.distance 运算在where中还是排序效率都会差一点

Answer 6

select number,A.distance*
(select top 1 factor from B where B.distance>A.distance and B.number=A.number order  by B.distance)
cost,month from A