Firebird 3交叉表统计问题：如何按月份字段统计并转置显示？

Got it, let's break down why your current PAID_LISTING procedure isn't delivering the monthly counts you need. The core issue right now is that your generic COUNT(A.PAYROLL_PAYMONTH) is tallying all payroll records for an employee in the specified year, not filtering to count only records from a specific month like January.

Key Fix: Use Conditional Aggregation #

To get per-month counts (including showing 0 when there are no records for a month), you'll use CASE WHEN inside your COUNT statements to target each month individually. Here's how to adjust your procedure step by step:

1. Add return parameters for every month you want to display (I'll include JAN, FEB, MAR as examples matching your sample data).
2. Replace the generic COUNT with month-specific conditional counts.
3. Fix a likely typo: Your WHERE clause used A.PAYROLL_YEAR, but your table field is PAYROLL_PAYYEAR—this was probably causing mismatched results too.

Modified Stored Procedure Code #

CREATE PROCEDURE PAID_LISTING(
    SORT_PAYROLL_YEAR VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1
) RETURNS(
    EMP_SURNAME VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
    PAYROLL_PAYYEAR VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
    PAYROLL_MON_JAN INTEGER,
    PAYROLL_MON_FEB INTEGER,
    PAYROLL_MON_MAR INTEGER
) AS
BEGIN
    FOR 
        SELECT 
            B.EMP_SURNAME,
            A.PAYROLL_PAYYEAR,
            -- Count only records where month is JAN
            COUNT(CASE WHEN A.PAYROLL_PAYMONTH = 'JAN' THEN 1 END) AS PAYROLL_MON_JAN,
            -- Count only records where month is FEB
            COUNT(CASE WHEN A.PAYROLL_PAYMONTH = 'FEB' THEN 1 END) AS PAYROLL_MON_FEB,
            -- Count only records where month is MAR (returns 0 if none exist)
            COUNT(CASE WHEN A.PAYROLL_PAYMONTH = 'MAR' THEN 1 END) AS PAYROLL_MON_MAR
        FROM PAYROLL A
        JOIN EMP B ON A.EMP_PK = B.EMP_PK
        WHERE A.PAYROLL_PAYYEAR = :SORT_PAYROLL_YEAR
        GROUP BY B.EMP_SURNAME, A.PAYROLL_PAYYEAR
        ORDER BY B.EMP_SURNAME ASC
        INTO :EMP_SURNAME, :PAYROLL_PAYYEAR, :PAYROLL_MON_JAN, :PAYROLL_MON_FEB, :PAYROLL_MON_MAR
    DO
    BEGIN
        SUSPEND;
    END
END;

Why This Works #

• The CASE WHEN checks if PAYROLL_PAYMONTH matches the target month. If it does, it returns 1 (a non-null value); otherwise, it returns NULL.
• COUNT ignores NULL values, so it only counts records that match the month condition. If there are no records for a month, it returns 0—exactly what you need for cases like MAR in your sample data.
• I switched the return type of month columns to INTEGER (your original used VARCHAR, which isn't ideal for numeric counts).
• Switched to explicit JOIN syntax for better readability (this is optional but makes the code easier to maintain).

Expected Output #

When you run this procedure with SORT_PAYROLL_YEAR = '1999', you'll get a result like this:

Which perfectly matches the output you described.

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