【LeetCode 3401. Find Circular Gift Exchange Chains】[mysql/pgsql/oracle/t-sql] with recursive CTE找到图中所有的环

题目地址

https://leetcode.cn/problems/find-circular-gift-exchange-chains/description/

代码

MySQL

WITH RECURSIVE GiftChain AS (
    -- Base case: Start with each giver
    SELECT 
        giver_id,
        receiver_id,
        CONCAT(giver_id, ' -> ', receiver_id) AS path,
        1 AS chain_length,
        gift_value as total_gift_value,
        least(giver_id, 100000) as path_least_node,
        giver_id AS start_id
    FROM SecretSanta
    
    UNION ALL
    
    -- Recursive case: Follow the gift chain
    SELECT 
        g.giver_id,
        s.receiver_id,
        CONCAT(g.path, ' -> ', s.receiver_id) AS path,
        g.chain_length + 1 AS chain_length,
        g.total_gift_value + gift_value  as total_gift_value,
        least(s.giver_id, g.path_least_node) as path_least_node,
        g.start_id
    FROM GiftChain g
    JOIN SecretSanta s ON g.receiver_id = s.giver_id
    WHERE g.receiver_id != g.start_id  -- Prevent premature loops
)
,
result as(
    SELECT 
        gc.path,
        gc.chain_length,
        gc.total_gift_value,
        gc.path_least_node
    FROM GiftChain gc
    WHERE gc.receiver_id = gc.start_id  -- Complete loops only
    ORDER BY gc.chain_length, gc.path
)



-- select * from result

select row_number() over(order by chain_length desc, total_gift_value desc) as chain_id ,
chain_length,total_gift_value
from (select distinct chain_length, total_gift_value from result) temp2
order by chain_length desc, total_gift_value desc

PostgresSQL

WITH RECURSIVE GiftChain AS (
    -- Base case: Start with each giver
    SELECT 
        giver_id,
        receiver_id,
        giver_id || ' -> ' || receiver_id AS path,
        1 AS chain_length,
        gift_value AS total_gift_value,
        LEAST(giver_id, 100000) AS path_least_node,
        giver_id AS start_id
    FROM SecretSanta
    
    UNION ALL
    
    -- Recursive case: Follow the gift chain
    SELECT 
        g.giver_id,
        s.receiver_id,
        g.path || ' -> ' || s.receiver_id AS path,
        g.chain_length + 1 AS chain_length,
        g.total_gift_value + s.gift_value AS total_gift_value,
        LEAST(s.giver_id, g.path_least_node) AS path_least_node,
        g.start_id
    FROM GiftChain g
    JOIN SecretSanta s ON g.receiver_id = s.giver_id
    WHERE g.receiver_id != g.start_id
),
result AS (
    SELECT 
        gc.path,
        gc.chain_length,
        gc.total_gift_value,
        gc.path_least_node
    FROM GiftChain gc
    WHERE gc.receiver_id = gc.start_id
    ORDER BY gc.chain_length, gc.path
)

SELECT 
    ROW_NUMBER() OVER (ORDER BY chain_length DESC, total_gift_value DESC) AS chain_id,
    chain_length,
    total_gift_value
FROM (SELECT DISTINCT chain_length,total_gift_value FROM result) temp2
ORDER BY chain_length DESC, total_gift_value DESC;

Oracle

WITH GiftChain (giver_id, receiver_id, path, chain_length, total_gift_value, path_least_node, start_id) AS (
    -- Base case: Start with each giver
    SELECT 
        giver_id,
        receiver_id,
        giver_id || ' -> ' || receiver_id AS path,
        1 AS chain_length,
        gift_value AS total_gift_value,
        LEAST(giver_id, 100000) AS path_least_node,
        giver_id AS start_id
    FROM SecretSanta
    
    UNION ALL
    
    -- Recursive case: Follow the gift chain
    SELECT 
        s.giver_id,
        s.receiver_id,
        g.path || ' -> ' || s.receiver_id AS path,
        g.chain_length + 1 AS chain_length,
        g.total_gift_value + s.gift_value AS total_gift_value,
        LEAST(s.giver_id, g.path_least_node) AS path_least_node,
        g.start_id
    FROM GiftChain g
    JOIN SecretSanta s ON g.receiver_id = s.giver_id
    WHERE s.giver_id != g.start_id  -- Prevent premature loops
)
SELECT 
    ROW_NUMBER() OVER (ORDER BY chain_length DESC, total_gift_value DESC) AS chain_id,
    chain_length,
    total_gift_value
FROM (
    SELECT DISTINCT 
        chain_length, 
        total_gift_value
    FROM GiftChain
    WHERE receiver_id = start_id  -- Complete loops only
)
ORDER BY 
    chain_length DESC, 
    total_gift_value DESC;

T-SQL

WITH GiftChain (giver_id, receiver_id, path, chain_length, total_gift_value, path_least_node, start_id) AS (
    -- Base case: Start with each giver
    SELECT 
        giver_id,
        receiver_id,
        CAST(CAST(giver_id AS VARCHAR(100)) + ' -> ' + CAST(receiver_id AS VARCHAR(100)) AS VARCHAR(100)) AS path,
        1 AS chain_length,
        gift_value AS total_gift_value,
        CASE WHEN giver_id < 100000 THEN giver_id ELSE 100000 END AS path_least_node,
        giver_id AS start_id
    FROM SecretSanta
    
    UNION ALL
    
    -- Recursive case: Follow the gift chain
    SELECT 
        s.giver_id,
        s.receiver_id,
        CAST(g.path + ' -> ' + CAST(s.receiver_id AS VARCHAR(100)) AS VARCHAR(100)) AS path,
        g.chain_length + 1 AS chain_length,
        g.total_gift_value + s.gift_value AS total_gift_value,
        CASE WHEN s.giver_id < g.path_least_node THEN s.giver_id ELSE g.path_least_node END AS path_least_node,
        g.start_id
    FROM GiftChain g
    JOIN SecretSanta s ON g.receiver_id = s.giver_id
    WHERE s.giver_id <> g.start_id  -- Prevent premature loops
)
SELECT 
    ROW_NUMBER() OVER (ORDER BY chain_length DESC, total_gift_value DESC) AS chain_id,
    chain_length,
    total_gift_value
FROM (
    SELECT DISTINCT 
        chain_length, 
        total_gift_value
    FROM GiftChain
    WHERE receiver_id = start_id  -- Complete loops only
) AS completed_chains
ORDER BY 
    chain_length DESC, 
    total_gift_value DESC;