如何在现有多表关联分组SQL查询中新增按人员统计月度有效记录数的列？

To add the ACTIVE column that counts each person's valid records, you have two efficient approaches depending on your performance needs and SQL dialect. Here's how to modify your query:

Key Notes First #

• FROM is a reserved SQL keyword, so wrap it in quotes ("FROM") to avoid syntax errors.
• The original CASE statement had a typo ('JES' → corrected to 'YES' to match your example).
• FIRST_DAY() and LAST_DAY() are assumed to be valid functions in your SQL environment (adjust if using dialects like PostgreSQL, e.g., DATE_TRUNC('month', CURRENT_DATE) for the first day of the month).

Approach 1: Correlated Subquery (Simple & Readable) #

This approach calculates the active count directly in the SELECT clause by querying the SALARY table for each person's valid records:

SELECT 
    P.NAME, 
    L.ID, 
    L."FROM", 
    L.END, 
    L.SALARY, 
    L.NOTE, 
    CASE WHEN (MAX(A.END) IS NULL OR MAX(A.END) >= CURRENT_DATE) THEN 'YES' ELSE 'NO' END AS "HAVE ONE",
    -- Count valid records for the person
    (SELECT COUNT(*) 
     FROM SALARY L2 
     WHERE L2.ID = P.ID 
       AND L2.END >= FIRST_DAY(CURRENT_DATE) 
       AND L2."FROM" <= LAST_DAY(CURRENT_DATE)) AS ACTIVE
FROM SALARY L 
INNER JOIN CONTACT A ON A.ID = L.ID 
INNER JOIN Pep P ON P.ID = L.ID 
WHERE L.SALARY = '8000' AND L.END >= CURRENT_DATE 
GROUP BY P.NAME, L.ID, L."FROM", L.END, L.SALARY, L.NOTE

Approach 2: CTE with Join (More Efficient for Large Datasets) #

This precomputes all active counts once using a Common Table Expression (CTE), then joins it to your main query—ideal if you have many rows:

WITH ActiveCounts AS (
    -- Precalculate valid record counts per person
    SELECT 
        ID, 
        COUNT(*) AS ACTIVE
    FROM SALARY
    WHERE END >= FIRST_DAY(CURRENT_DATE) 
      AND "FROM" <= LAST_DAY(CURRENT_DATE)
    GROUP BY ID
)
SELECT 
    P.NAME, 
    L.ID, 
    L."FROM", 
    L.END, 
    L.SALARY, 
    L.NOTE, 
    CASE WHEN (MAX(A.END) IS NULL OR MAX(A.END) >= CURRENT_DATE) THEN 'YES' ELSE 'NO' END AS "HAVE ONE",
    COALESCE(AC.ACTIVE, 0) AS ACTIVE -- Show 0 if no valid records exist
FROM SALARY L 
INNER JOIN CONTACT A ON A.ID = L.ID 
INNER JOIN Pep P ON P.ID = L.ID 
LEFT JOIN ActiveCounts AC ON AC.ID = P.ID
WHERE L.SALARY = '8000' AND L.END >= CURRENT_DATE 
GROUP BY P.NAME, L.ID, L."FROM", L.END, L.SALARY, L.NOTE, AC.ACTIVE

How It Works #

• Both queries count records where L.END is on or after the first day of the current month, and L.FROM is on or before the last day of the current month.
• The ACTIVE value is consistent across all rows for the same person (matches your example: TINA’s rows show 2, KLAR’s show 1).
• The COALESCE in Approach 2 ensures ACTIVE shows 0 instead of NULL if a person has no valid records.

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