如何制作一个带有不等式的SQL Join，但只为不等式的每一个马赫选择前1行

Question

我有这些表：

表A:

id	值
1	20
2	15
3	10

您可以使用ROW_NUMBER()(在MySQL 8.x中可用(。例如：

select *
from (
select
a.id as a_id, a.value as a_value,
b.id as b_id, b.value as b_value,
row_number() over(partition by a.id order by b.value desc) as rn
from a
join b on a.id = b.id
and a.value >= b.value
) x
where rn = 1