SqlServer查询

[删库到跑路]

A
ID	小于运距	Num	单价	BeginTime	rowno
51	15.00	17.00	25.00	2019-05-26 00:00:00.000	1
51	25.00	17.00	28.00	2019-07-03 00:00:00.000	1
51	35.00	17.00	30.00	2019-07-11 00:00:00.000	1

B
ID      运距      
51       26
51       20


展示结果
ID     运距      单价
51     26         30
51     20         28


求语句，谢谢

[删库到跑路]

求方子啊

[文盲老顾]

with a as (
	select 51 as id,15.0 as 小于运距,17.0 as num,25.0 as 单价
	union all select 51,25,17,28
	union all select 51,35,17,30
),b as (
	select 51 as id,26.0 as 运距
	union all select 51,20
)
select * from b
cross apply (
	select min(单价) as 单价 from a where id=b.id and 小于运距>=b.运距
) x

[yolyry]

--楼上正解，或者用With CTE的方式也可以，不过cross apply相对容易理解些
use tempdb
if not object_id(N'Tempdb..A') is null
    drop table A
Go
Create table A([id] int,S int,num int,price int,begt datetime,rowno int)
Insert A
select 51,15,17,25,2019-05-26,1 union all
select 51,25,17,28,2019-07-03,1 union all
select 51,35,17,30,2019-07-11,1
if not object_id(N'Tempdb..#B') is null
    drop table B
Go
if not object_id(N'Tempdb..B') is null
    drop table B
Create table B([id] int,S int)
Insert B
select 51,26 union all
select 51,20

select * from b cross apply(select MIN(price) price from a where a.id=b.id and a.price>=b.S )c