Oracle唯一记录检索：无循环实现多表关联特定格式输出

To achieve the desired output format where each address and degree entry is displayed on separate rows (with NULLs for non-matching related data), you can use window functions to assign row numbers to each user's address and degree records, then use full outer joins to combine all datasets. Here's how to do it:

Step-by-Step Explanation #

1. Assign Row Numbers: Use ROW_NUMBER() to generate a sequential number for each address and degree belonging to the same user. This helps align related records across tables.
2. Full Outer Joins: Combine the Users table with the numbered address/degree datasets. This ensures all records are retained, even if a user has more addresses than degrees (or vice versa).
3. Conditional User Fields: Use CASE WHEN to only show user details on the first row for each user; subsequent rows for the same user will display NULL for user fields, matching your required output.

Complete SQL Query #

SELECT 
    -- Show user details only on the first row per user
    CASE WHEN COALESCE(ua.row_num, ud.row_num) = 1 THEN u.UserID ELSE NULL END AS UserID,
    CASE WHEN COALESCE(ua.row_num, ud.row_num) = 1 THEN u.UserName ELSE NULL END AS UserName,
    -- Address fields
    ua.UserAddressID, 
    ua.UserID AS addr_UserID, 
    ua.Address,
    -- Degree fields
    ud.UserDegreeID, 
    ud.UserID AS deg_UserID, 
    ud.Degree
FROM 
    Users u
    -- Join with numbered address records
    FULL OUTER JOIN (
        SELECT 
            UserAddressID, 
            UserID, 
            Address,
            ROW_NUMBER() OVER(PARTITION BY UserID ORDER BY UserAddressID) AS row_num
        FROM UserAddress
    ) ua ON u.UserID = ua.UserID
    -- Join with numbered degree records, matching both user ID and row number
    FULL OUTER JOIN (
        SELECT 
            UserDegreeID, 
            UserID, 
            Degree,
            ROW_NUMBER() OVER(PARTITION BY UserID ORDER BY UserDegreeID) AS row_num
        FROM UserDegree
    ) ud ON u.UserID = ud.UserID AND ua.row_num = ud.row_num
-- Sort to match the desired output order
ORDER BY 
    COALESCE(u.UserID, ua.UserID, ud.UserID), 
    COALESCE(ua.row_num, ud.row_num);

Output Verification #

When executed against your sample tables, this query will produce exactly the output you requested:

UserID UserName UserAddressID addr_UserID Address  UserDegreeID deg_UserID Degree
1      Ramesh   1             1           South St 1            1          BSC
NULL   NULL     2             1           North St NULL         NULL       NULL
2      Suresh   3             2           New St   2            2          B.Tech
NULL   NULL     NULL          NULL        NULL     3            2          M.Tech

This approach avoids any loops or procedural code, relying entirely on set-based SQL operations which are efficient and idiomatic for Oracle.

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