MySQL实现捐赠者月度捐赠统计：行列转换及数组列返回问询

Hey there! Let's work through your NGO's donor donation status display problem. I'll cover two approaches you mentioned: pivoting the data to show monthly 1/0 statuses, and creating a temporary column with donated months as an array-like string. First, let's assume your table structures look like this (adjust if yours differ slightly):

• Donors: donor_id (primary key), donor_name
• Donation_Months: donor_id (foreign key to Donors), donation_month (formatted like 'YYYY-MM'), is_donated (1 = donated, 0 = not donated) — or if this table only records months where a donor did donate, we'll adjust the logic accordingly.

This approach converts rows of monthly donation data into columns, showing each donor's status for every month.

Fixed List of Months (e.g., 2024 Calendar Year) #

If you know the specific months you want to display, use a CASE statement with aggregation to pivot the data:

SELECT
    d.donor_name,
    MAX(CASE WHEN dm.donation_month = '2024-01' THEN COALESCE(dm.is_donated, 0) END) AS Jan_2024,
    MAX(CASE WHEN dm.donation_month = '2024-02' THEN COALESCE(dm.is_donated, 0) END) AS Feb_2024,
    MAX(CASE WHEN dm.donation_month = '2024-03' THEN COALESCE(dm.is_donated, 0) END) AS Mar_2024,
    MAX(CASE WHEN dm.donation_month = '2024-04' THEN COALESCE(dm.is_donated, 0) END) AS Apr_2024,
    MAX(CASE WHEN dm.donation_month = '2024-05' THEN COALESCE(dm.is_donated, 0) END) AS May_2024,
    MAX(CASE WHEN dm.donation_month = '2024-06' THEN COALESCE(dm.is_donated, 0) END) AS Jun_2024,
    MAX(CASE WHEN dm.donation_month = '2024-07' THEN COALESCE(dm.is_donated, 0) END) AS Jul_2024,
    MAX(CASE WHEN dm.donation_month = '2024-08' THEN COALESCE(dm.is_donated, 0) END) AS Aug_2024,
    MAX(CASE WHEN dm.donation_month = '2024-09' THEN COALESCE(dm.is_donated, 0) END) AS Sep_2024,
    MAX(CASE WHEN dm.donation_month = '2024-10' THEN COALESCE(dm.is_donated, 0) END) AS Oct_2024,
    MAX(CASE WHEN dm.donation_month = '2024-11' THEN COALESCE(dm.is_donated, 0) END) AS Nov_2024,
    MAX(CASE WHEN dm.donation_month = '2024-12' THEN COALESCE(dm.is_donated, 0) END) AS Dec_2024
FROM Donors d
LEFT JOIN Donation_Months dm 
    ON d.donor_id = dm.donor_id
GROUP BY d.donor_id, d.donor_name;

• LEFT JOIN ensures every donor is included, even if they have no donations.
• COALESCE handles cases where a donor has no record for a month (returns 0 instead of NULL).
• MAX aggregates the single value per donor-month, since each donor should only have one status per month.

Dynamic Months (Auto-Adjust to All Existing Months) #

If you want the query to automatically include all months present in Donation_Months, use a stored procedure to build dynamic SQL:

DELIMITER //

CREATE PROCEDURE GetDynamicDonorStatus()
BEGIN
    DECLARE month_columns TEXT DEFAULT '';
    DECLARE done INT DEFAULT 0;
    DECLARE current_month VARCHAR(7);

    -- Cursor to fetch all unique months from Donation_Months
    DECLARE month_cursor CURSOR FOR
        SELECT DISTINCT donation_month 
        FROM Donation_Months 
        ORDER BY donation_month;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- Build the CASE statement columns
    OPEN month_cursor;
    month_loop: LOOP
        FETCH month_cursor INTO current_month;
        IF done THEN
            LEAVE month_loop;
        END IF;
        SET month_columns = CONCAT(
            month_columns,
            ', MAX(CASE WHEN dm.donation_month = ''', current_month, ''' THEN COALESCE(dm.is_donated, 0) END) AS ''', current_month, ''''
        );
    END LOOP;
    CLOSE month_cursor;

    -- Assemble and execute the full query
    SET @full_query = CONCAT(
        'SELECT d.donor_name', month_columns,
        ' FROM Donors d LEFT JOIN Donation_Months dm ON d.donor_id = dm.donor_id',
        ' GROUP BY d.donor_id, d.donor_name'
    );

    PREPARE stmt FROM @full_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

-- Run the procedure to get dynamic results
CALL GetDynamicDonorStatus();

This will automatically add a column for every unique month in your Donation_Months table, no manual updates needed.

If you prefer to list each donor's donated months as a comma-separated string (which acts like an array in many applications), use GROUP_CONCAT:

SELECT
    d.donor_name,
    -- Comma-separated list of donated months (e.g., "2024-01,2024-03")
    GROUP_CONCAT(DISTINCT dm.donation_month ORDER BY dm.donation_month) AS donated_months,
    -- Optional: Format as a JSON array (e.g., ["2024-01","2024-03"])
    CONCAT('[', GROUP_CONCAT(DISTINCT '"', dm.donation_month, '"' ORDER BY dm.donation_month), ']') AS donated_months_json_array
FROM Donors d
LEFT JOIN Donation_Months dm 
    ON d.donor_id = dm.donor_id
GROUP BY d.donor_id, d.donor_name;

• GROUP_CONCAT aggregates all donated months into a single string.
• Adding quotes and wrapping in brackets creates a valid JSON array, which is easy to parse in most programming languages.

If you also want a string representing 1/0 status for all months (e.g., "1,0,1,0..."), cross-join with a list of all months first:

SELECT
    d.donor_name,
    GROUP_CONCAT(
        CASE WHEN dm.donation_month IS NOT NULL THEN '1' ELSE '0' END
        ORDER BY m.month_val
    ) AS monthly_status_string
FROM Donors d
-- Generate all months for the year (adjust the date range as needed)
CROSS JOIN (
    SELECT DATE_FORMAT('2024-01-01' + INTERVAL (n-1) MONTH, '%Y-%m') AS month_val
    FROM (
        SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
        UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
    ) month_numbers
) m
LEFT JOIN Donation_Months dm 
    ON d.donor_id = dm.donor_id AND dm.donation_month = m.month_val
GROUP BY d.donor_id, d.donor_name;

A common pitfall with subqueries here is failing to properly aggregate or join all necessary rows — the approaches above avoid that by using LEFT JOIN and aggregation functions to ensure every donor and month is accounted for.

内容的提问来源于stack exchange，提问作者eng.ahmed