求一条联合查询语句
表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
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
楼主的结果有一个错误
[解决办法]
厉害。来接分
[解决办法]
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 行）
