JPA中PROPOSAL与SUB_PROMOTION表的递归SQL查询需求实现

Got it, let's work through this recursive lookup problem. The goal is to start from a specific SUB_PROMOTION_ID, check if it has linked PROPOSAL records, and if not, climb up the parent hierarchy until we find one that does (or hit the root node if none exist).

Solution Using Recursive CTE #

Most modern databases (PostgreSQL, MySQL 8+, SQL Server, etc.) support Recursive Common Table Expressions (CTEs), which are perfect for this kind of hierarchical traversal. Here's a query that implements your exact logic:

WITH RECURSIVE promotion_hierarchy AS (
    -- Anchor member: Start with your target SUB_PROMOTION_ID
    SELECT
        sp.SUB_PROMOTION_ID AS current_id,
        sp.PARENT_SUB_PROMOTION_ID AS parent_id,
        -- Check if this ID has any linked proposals
        EXISTS(SELECT 1 FROM PROPOSAL p WHERE p.SUB_PROMOTION_ID = sp.SUB_PROMOTION_ID) AS has_proposal
    FROM SUB_PROMOTION sp
    WHERE sp.SUB_PROMOTION_ID = :target_id -- Replace with your specific ID

    UNION ALL

    -- Recursive member: Traverse up to parent only if current node has no proposals
    SELECT
        sp.SUB_PROMOTION_ID AS current_id,
        sp.PARENT_SUB_PROMOTION_ID AS parent_id,
        EXISTS(SELECT 1 FROM PROPOSAL p WHERE p.SUB_PROMOTION_ID = sp.SUB_PROMOTION_ID) AS has_proposal
    FROM SUB_PROMOTION sp
    JOIN promotion_hierarchy ph ON sp.SUB_PROMOTION_ID = ph.parent_id
    WHERE ph.has_proposal = FALSE
)
-- Pick the first valid result: either the closest node with proposals, or the root
SELECT current_id AS result_id
FROM promotion_hierarchy
WHERE has_proposal = TRUE OR parent_id IS NULL
ORDER BY 
    has_proposal DESC, -- Prioritize nodes with proposals
    (SELECT COUNT(*) FROM promotion_hierarchy ph2 WHERE ph2.current_id = promotion_hierarchy.current_id) ASC -- Get the closest match first
LIMIT 1;

How This Works #

Let's break down the logic step by step:

• Anchor Member: We start with your target SUB_PROMOTION_ID, fetch its parent ID, and check if it has any associated PROPOSAL records using EXISTS (this is efficient because it stops searching as soon as it finds a match).
• Recursive Member: If the current node has no linked proposals, we move up to its parent and repeat the check. This continues only for nodes that don't meet the proposal condition.
• Final Selection: We filter the hierarchy to get either nodes with proposals or the root node (where parent_id is null). We sort to prioritize nodes with proposals first, and pick the closest one to your target ID using LIMIT 1.

Example Scenarios #

• If your target ID 100 has no proposals, but its parent 90 also has none, and grandparent 80 has proposals: the query returns 80.
• If none of the nodes in the hierarchy have proposals, it returns the root node (the topmost ID with no parent).

内容的提问来源于stack exchange，提问作者Deepjyoti De