SQL查询：计算指定portfolio_id关联的所有资金总余额

Got it, let's work through this problem. You need to calculate the total fund balance for every portfolio directly tied to P1, plus any sub-portfolios that eventually trace up to P1 (including P2, P3, P4 as you noted).

First, let's assume your schema has two key tables (adjust if your actual tables are named differently):

• portfolio_hierarchy: Tracks parent-child portfolio links, with columns like portfolio_id (parent) and sub_portfolio_id (child)
• fund_balances: Stores each portfolio's balance, with portfolio_id and balance columns

The trick here is using a recursive CTE to traverse the entire portfolio tree starting from P1—this lets us grab all related portfolios, no matter how deep the hierarchy goes. Then we just sum up their balances.

Here's the SQL query you can use:

WITH RECURSIVE related_portfolios AS (
    -- Start with our target portfolio, P1
    SELECT portfolio_id
    FROM fund_balances
    WHERE portfolio_id = 'P1'
    
    UNION ALL
    
    -- Recursively pull in all sub-portfolios of the portfolios we've already found
    SELECT h.sub_portfolio_id
    FROM portfolio_hierarchy h
    INNER JOIN related_portfolios rp 
        ON h.portfolio_id = rp.portfolio_id
)
-- Calculate the total balance across all related portfolios
SELECT SUM(balance) AS total_fund_balance
FROM fund_balances fb
INNER JOIN related_portfolios rp 
    ON fb.portfolio_id = rp.portfolio_id;

If your hierarchy table uses parent_portfolio_id instead of portfolio_id for the parent entry, tweak the recursive part like this:

SELECT h.sub_portfolio_id
FROM portfolio_hierarchy h
INNER JOIN related_portfolios rp 
    ON h.parent_portfolio_id = rp.portfolio_id

A quick few tips:

• If your portfolio hierarchy could have cycles (unlikely, but possible), add a column to track the path of portfolios we've already visited to avoid infinite recursion
• If some sub-portfolios don't have a balance entry, swap the INNER JOIN for a LEFT JOIN and use COALESCE(SUM(balance), 0) to return 0 instead of NULL
• Make sure to replace table/column names with your actual schema's names if they don't match

This query will capture P1, all its direct sub-portfolios, and any nested sub-portfolios that trace back to P1, then sum up all their balances in one go.

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