create table A(
id int,
产品名称 varchar(10),
拍卖数量 int,
底价 int
)
insert into A
select 1 ,'产品2', 3, 10


create table B(
id int,
姓名 varchar(20),
出价 int
)
insert into B
      select 1 ,'张三', 20
union select 1 ,'李四', 19
union select 1, '王五', 17
union select 1 ,'赵六', 15
union select 1 ,'钱七' ,13

select * from A


select * from B

with T as(
select a.产品名称,a.底价 ,b.出价,ROW_NUMBER() over(order by b.出价 desc)as max_p,ROW_NUMBER() over(order by b.出价 asc)as min_p from A a join B b on a.id=b.id
where b.出价>=a.底价
group by a.产品名称,a.底价,b.出价)
select a.产品名称,a.底价, a.出价 as 最高出价,B.出价  as 有效最低出价 from T a,T b
where  a.max_p=1 and b.min_p=1 and a.产品名称=b.产品名称 and B.出价>=a.底价

//执行结果
产品名称    底价    最高出价    有效最低出价
产品2    10    20    13