sql - 在SQL中递归查找最小对值

我有桌子

   ID   MIN_ID
   1    1
   2    1
   3    1
   4    4
   5    1
   6    5
   7    6
   8    4

我需要第三列final_id，如果id=min_id，它将返回min_id，否则它将在表中递归查找，直到找到id=min_id的行。

   ID   MIN_ID  FINAL_ID
   1    1       1
   2    1       1
   3    1       1
   4    4       4
   5    1       1
   6    5       1        // 5 have the MIN_ID of 1
   7    6       1       // 6 have the MIN_ID of 1 and 5 have the MIN_ID of 1.
   8    4       4       // 8 goes to 4, 4 goes to 4. Done.

问题是，如果不在sql中使用任何过程语言，我如何才能得到这个结果。任何dbms示例都适合（mysql、postgresql、db2、sql server、oracle）
例如，执行此操作的php代码：

<?php

$table = [
    ['ID' => '1', 'MIN_ID' => '1'],
    ['ID' => '2', 'MIN_ID' => '1'],
    ['ID' => '3', 'MIN_ID' => '1'],
    ['ID' => '4', 'MIN_ID' => '4'],
    ['ID' => '5', 'MIN_ID' => '1'],
    ['ID' => '6', 'MIN_ID' => '5'],
    ['ID' => '7', 'MIN_ID' => '6'],
];


for ($x = 0; $x < count($table); $x++) {
    $table[$x]['FINAL_ID'] = calculate_final_id($table, $table[$x]);
}

print_r($table);


function calculate_final_id($table, $row)
{

    if ($row['ID'] == $row['MIN_ID']) {
        return $row['ID'];
    }

    $next_row = search($table, $row['MIN_ID']);

    return calculate_final_id($table, $next_row);
}

function search($table, $id)
{
    for ($x = 0; $x < count($table); $x++) {
        if ($table[$x]['ID'] == $id) {
            return $table[$x];
        }
    }
}

最佳答案

SQL Server示例。基于ID的顺序

CREATE TABLE myTable
    (ID int, MIN_ID int);

INSERT INTO myTable
    (ID, MIN_ID)
VALUES
    (1, 1),
    (2, 1),
    (3, 1),
    (4, 4),
    (5, 1),
    (6, 5),
    (7, 6)
;
WITH rc AS (
    SELECT ID, MIN_ID as FinalId
    FROM myTable
    UNION ALL
    SELECT rc.ID, t.MIN_ID
    FROM rc
    JOIN myTable t ON rc.FinalId = t.ID AND t.ID != t.MIN_ID
)
SELECT ID, min(FinalId) FinalId
FROM rc
GROUP BY ID;