如何通过JOIN查询获取对应最小rnode值的final_mlm表记录ID

咱先理清楚逻辑：你原来的查询已经能算出目标最小rnode值了，接下来只要找到表中rnode等于这个值的记录ID就行，下面给你两种常用的实现方式：

方法一：子查询获取最小值后匹配 #

这种方式兼容性拉满，几乎所有关系型数据库都能跑：

SELECT id
FROM final_mlm
WHERE rnode = (
    -- 复用你原来的查询逻辑，只提取最小的rnode值
    SELECT MIN(node.rnode)
    FROM final_mlm AS node, final_mlm AS parent 
    WHERE node.lnode BETWEEN parent.lnode AND parent.rnode 
      AND parent.id = 1 
      AND node.placement='l'
)

如果有多个记录的rnode都等于这个最小值，这条语句会返回所有对应的ID。要是你还需要这些记录的其他字段，把SELECT id改成SELECT *就行。

方法二：用窗口函数更高效（适合支持窗口函数的数据库） #

如果你的数据库是MySQL 8.0+、PostgreSQL、SQL Server这类支持窗口函数的，可以用这种方式，一次查询就能搞定：

SELECT id
FROM (
    SELECT 
        node.id,
        node.rnode,
        -- 计算当前结果集中的最小rnode
        MIN(node.rnode) OVER () AS min_rnode
    FROM final_mlm AS node
    -- 用JOIN语法替代旧的逗号连接，可读性更好
    JOIN final_mlm AS parent 
        ON node.lnode BETWEEN parent.lnode AND parent.rnode
    WHERE parent.id = 1 
      AND node.placement='l'
) AS sub_query
-- 筛选出rnode等于最小值的记录
WHERE rnode = min_rnode;

同样，要是需要其他字段，直接在子查询里加上，外层查询一起返回就行。

内容的提问来源于stack exchange，提问作者Mad Coder