Oracle SQL需求：统计关联表中各PersonID对应值的出现次数

Hey there! Sorry to hear you've been stuck rewriting this query 20 times—frustrating when things don't click, especially with a database you're still getting the hang of. Let's break this down for Oracle SQL, since you're working with a one-to-many relationship between the People and Values tables.

Method 1: Using CASE Statements with Aggregation #

This is a straightforward approach that works across most SQL databases, including Oracle. We'll group by PersonID and use COUNT() with CASE to tally each specific value (1,2,3,4):

SELECT
    p.PersonID,
    COUNT(CASE WHEN v.Value = 1 THEN 1 END) AS value_1_count,
    COUNT(CASE WHEN v.Value = 2 THEN 1 END) AS value_2_count,
    COUNT(CASE WHEN v.Value = 3 THEN 1 END) AS value_3_count,
    COUNT(CASE WHEN v.Value = 4 THEN 1 END) AS value_4_count
FROM
    People p
LEFT JOIN
    Values v ON p.PersonID = v.PersonID
GROUP BY
    p.PersonID;

• We use a LEFT JOIN to make sure we include people who have no matching records in the Values table (their counts will show as 0).
• The CASE statement returns 1 only when the value matches, otherwise it returns NULL. COUNT() ignores NULL values, so it only counts the matches for each value.

Method 2: Using Oracle's PIVOT Function #

Oracle has a built-in PIVOT operator that's perfect for this kind of row-to-column transformation. It's more concise if you're comfortable with Oracle-specific syntax:

SELECT
    PersonID,
    "1" AS value_1_count,
    "2" AS value_2_count,
    "3" AS value_3_count,
    "4" AS value_4_count
FROM (
    SELECT p.PersonID, v.Value
    FROM People p
    LEFT JOIN Values v ON p.PersonID = v.PersonID
)
PIVOT (
    COUNT(Value)
    FOR Value IN (1, 2, 3, 4)
);

• The subquery gets the raw PersonID and Value pairs.
• The PIVOT clause aggregates (counts) the values and turns each unique value into a column. We alias the columns to make them more readable.

Why Your Subqueries Might Have Failed #

If you tried 4 separate subqueries (one per value), the issue was likely either:

• Not properly correlating the subqueries to the main People table (leading to incorrect counts or missing rows),
• Using INNER JOIN instead of LEFT JOIN (excluding people with no matching values),
• Or inefficiently joining the same Values table multiple times (which can cause performance issues and unexpected results).

Either of the methods above should fix your problem—give them a try!

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