SQL技术问询：实现两张表多层关联并完成COUNT统计

Hey there! Let's break down why your query is returning inflated counts and how to fix it.

The Root Cause #

Your current query uses three LEFT JOIN operations on the same ligauruguaya_v table—one for each column (primero, segundo, tercero). Here's the problem: when a user has matches in multiple of these columns, the joins create a cartesian product of those matching rows. For example, if a user has 2 rows in primero and 3 rows in segundo, the join will produce 2*3=6 rows total. When you run COUNT(pepe.primero), it counts all 6 rows instead of just the original 2—hence the duplicate counts.

Solution 1: Pre-Aggregate with Subqueries (Recommended for Performance) #

Instead of joining first and then counting, we'll count each category separately in subqueries, then join those aggregated results to the users table. This avoids the cartesian product entirely:

SELECT 
    u.nombre, 
    u.apellido,
    u.id,
    COALESCE(p.primero_count, 0) AS Primero,
    COALESCE(s.segundo_count, 0) AS Segundo,
    COALESCE(t.tercero_count, 0) AS Tercero
FROM users u
LEFT JOIN (
    SELECT primero, COUNT(*) AS primero_count
    FROM ligauruguaya_v
    GROUP BY primero
) p ON u.id = p.primero
LEFT JOIN (
    SELECT segundo, COUNT(*) AS segundo_count
    FROM ligauruguaya_v
    GROUP BY segundo
) s ON u.id = s.segundo
LEFT JOIN (
    SELECT tercero, COUNT(*) AS tercero_count
    FROM ligauruguaya_v
    GROUP BY tercero
) t ON u.id = t.tercero
WHERE u.categoria < 3
GROUP BY u.id, u.nombre, u.apellido

• COALESCE ensures that users with no matches in a category get a 0 instead of NULL.
• Each subquery calculates the count for its specific column independently, so there's no overlap or duplication between the counts.

Solution 2: Use COUNT(DISTINCT) (Simpler, Less Efficient for Large Data) #

If your ligauruguaya_v table has a unique identifier column (like an id), you can use COUNT(DISTINCT) to count only unique rows from each join:

SELECT 
    users.nombre, 
    users.apellido,
    users.id,
    COUNT(DISTINCT pepe.id) AS Primero,
    COUNT(DISTINCT pep.id) AS Segundo,
    COUNT(DISTINCT pe.id) AS Tercero
FROM users
LEFT JOIN ligauruguaya_v AS pepe ON users.id = pepe.primero
LEFT JOIN ligauruguaya_v AS pep ON users.id = pep.segundo
LEFT JOIN ligauruguaya_v as pe ON users.id = pe.tercero
WHERE users.categoria < 3
GROUP BY users.id, users.nombre, users.apellido

This works because even if the joins create duplicate rows, each original row in ligauruguaya_v has a unique id, so COUNT(DISTINCT id) ignores the duplicates and returns the correct number of matching rows.

Which Solution Should You Choose? #

• Use Solution 1 if you're working with large datasets: pre-aggregating in subqueries reduces the amount of data that needs to be joined, making the query faster.
• Use Solution 2 for smaller datasets or if you prefer a more concise query—just make sure you have a unique column to use with DISTINCT.

内容的提问来源于stack exchange，提问作者Daniel M. Faccioli