Oracle SQL多表数据组合查询：如何从5张表中获取指定格式的输出结果

Hey there! Let's figure out why your query isn't giving the expected output. The core issue is that your original LEFT JOIN approach tries to cram all attributes from different tables into a single row per user, but what you actually need is to split each attribute from each table into its own separate row—which is where UNION ALL comes in handy.

Here's the corrected SQL that matches your desired output:

-- 1. Records from Table2 (role as type)
SELECT 
    username, 
    role AS type,
    CASE WHEN role IN ('D', 'O', 'S') THEN 'Yes' ELSE 'No' END AS HP
FROM Table2

UNION ALL

-- 2. Records from Table3 (privilege as type)
SELECT 
    name AS username, 
    privilege AS type,
    CASE WHEN privilege IN ('CC', 'MS') THEN 'Yes' ELSE 'No' END AS HP
FROM Table3

UNION ALL

-- 3. Records from Table4 (split option1 and option2 into separate rows)
SELECT 
    username, 
    option1 AS type,
    CASE WHEN option1 = 'T' THEN 'Yes' ELSE 'No' END AS HP
FROM Table4

UNION ALL

SELECT 
    username, 
    option2 AS type,
    CASE WHEN option2 = 'T' THEN 'Yes' ELSE 'No' END AS HP
FROM Table4

UNION ALL

-- 4. Records from Table5 (privilege_1 as type)
SELECT 
    username, 
    privilege_1 AS type,
    CASE WHEN privilege_1 = 'U' AND AO = 'Yes' THEN 'Yes' ELSE 'No' END AS HP
FROM Table5

UNION ALL

-- 5. Add users from Table1 that don't appear in any other table
SELECT 
    username,
    NULL AS type,
    NULL AS HP
FROM Table1
WHERE username NOT IN (
    SELECT username FROM Table2
    UNION SELECT name FROM Table3
    UNION SELECT username FROM Table4
    UNION SELECT username FROM Table5
)

-- Optional: Order the results to match your expected output
ORDER BY username;

Let me break down why this works:

• UNION ALL combines results from multiple SELECT statements into one result set, keeping each row separate (which is exactly what you need for each attribute entry).
• Each section targets one table (or one attribute from Table4) and computes the HP value based on the specific rule for that data source.
• The final section adds the user 5 (and any other Table1 users not present in other tables) with null values for type and HP, as requested.

Your original query used LEFT JOINs which would merge all attributes for a single user into one row, rather than creating individual rows for each attribute. That's why you weren't getting the full list of rows in your desired output.

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