Oracle 11g中如何用SQL从三张表提取各分行储蓄账户最高余额客户数据

Hey there! Let's tackle this problem step by step. First, since the full structure of the Customer table isn't provided, we'll make a reasonable assumption for the relationships: we'll say the Customer table has cID (customer ID) and name (customer name), and it links to the Account table via the cID column. If your setup uses a junction table like Customer_Account(cID, accNum) instead, just tweak the join logic accordingly.

We can use the ROW_NUMBER() window function to rank savings accounts by balance within each branch, then pick the top-ranked one. We'll also join to get customer details and fetch the free overdraft limit from their current account.

WITH SavingsAccountRanked AS (
    SELECT 
        a.accNum,
        a.balance,
        a.bID,
        a.cID,
        -- Rank savings accounts in each branch by balance (highest first)
        ROW_NUMBER() OVER (PARTITION BY a.bID ORDER BY a.balance DESC) AS rank_num
    FROM Account a
    WHERE a.accType = 'SAVINGS' -- Adjust this to match your actual savings account type identifier
)
SELECT 
    c.name AS customer_name,
    sar.balance AS savings_account_balance,
    sar.bID AS branch_id,
    -- Get free overdraft limit from the customer's current account (pick first if multiple exist)
    (SELECT a.limitOfFreeOD 
     FROM Account a 
     WHERE a.cID = sar.cID AND a.accType = 'CURRENT'
     FETCH FIRST 1 ROW ONLY) AS current_account_free_overdraft_limit
FROM SavingsAccountRanked sar
JOIN Customer c ON sar.cID = c.cID
WHERE sar.rank_num = 1;

Key Notes: #

• ROW_NUMBER() groups accounts by bID (branch ID) and sorts each group by balance descending. The row with rank_num = 1 is the highest balance savings account for that branch.
• If a customer has multiple current accounts, the subquery uses FETCH FIRST 1 ROW ONLY to grab one—you can modify this to get the max, average, etc., based on your needs.
• If using a junction table, replace the JOIN Customer line with JOIN Customer_Account ca ON sar.accNum = ca.accNum JOIN Customer c ON ca.cID = c.cID.

Oracle 11g supports custom object types, which let you encapsulate results into structured objects. Here's how to implement this:

Step 1: Create a Custom Object Type #

First, define an object to hold our result data:

CREATE TYPE BranchTopCustomerType AS OBJECT (
    customer_name VARCHAR2(100),
    savings_balance NUMBER(15,2),
    branch_id VARCHAR2(20),
    current_free_overdraft_limit NUMBER(10,2)
);
/

Step 2: Query Using the Object Type #

We'll use the object constructor to wrap our query results into instances of BranchTopCustomerType:

WITH SavingsAccountRanked AS (
    SELECT 
        a.cID,
        a.balance,
        a.bID,
        ROW_NUMBER() OVER (PARTITION BY a.bID ORDER BY a.balance DESC) AS rank_num
    FROM Account a
    WHERE a.accType = 'SAVINGS'
)
SELECT 
    BranchTopCustomerType(
        c.name,
        sar.balance,
        sar.bID,
        (SELECT a.limitOfFreeOD 
         FROM Account a 
         WHERE a.cID = sar.cID AND a.accType = 'CURRENT'
         FETCH FIRST 1 ROW ONLY)
    ) AS top_customer_details
FROM SavingsAccountRanked sar
JOIN Customer c ON sar.cID = c.cID
WHERE sar.rank_num = 1;

Optional: Return a Nested Table of Objects #

If you want to return all results as a single nested table, create a nested table type first:

CREATE TYPE BranchTopCustomerListType AS TABLE OF BranchTopCustomerType;
/

SELECT CAST(MULTISET(
    WITH SavingsAccountRanked AS (
        SELECT 
            a.cID,
            a.balance,
            a.bID,
            ROW_NUMBER() OVER (PARTITION BY a.bID ORDER BY a.balance DESC) AS rank_num
        FROM Account a
        WHERE a.accType = 'SAVINGS'
    )
    SELECT 
        BranchTopCustomerType(
            c.name,
            sar.balance,
            sar.bID,
            (SELECT a.limitOfFreeOD 
             FROM Account a 
             WHERE a.cID = sar.cID AND a.accType = 'CURRENT'
             FETCH FIRST 1 ROW ONLY)
        )
    FROM SavingsAccountRanked sar
    JOIN Customer c ON sar.cID = c.cID
    WHERE sar.rank_num = 1
) AS BranchTopCustomerListType) AS branch_top_customers
FROM DUAL;

This returns a single collection containing all top customer objects.

• Make sure to replace 'SAVINGS' and 'CURRENT' with your actual account type values.
• If multiple customers tie for the highest savings balance in a branch, ROW_NUMBER() will only return one. Use RANK() or DENSE_RANK() instead if you want to include all tied customers.

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