MySQL：如何按列展示多日期未被预订工人的剩余供应量

Got it, let's tackle this pivot-style problem you're facing. The core goal is to turn row-wise daily availability stats into column-wise results—where each column shows the number of unbooked workers for a specific date—instead of running separate queries for each day. Here's a practical, MySQL-friendly approach:

First, Let's Assume Your Table Structure #

I'll work with common table names (adjust these to match your actual schema):

• workers: Stores worker details with worker_id and region (since you mentioned grouping by region)
• confirmed_bookings: Tracks booked workers with worker_id, booking_date (the date of the booking), and a status column (we'll use 'confirmed' to flag active bookings)

Step 1: Generate Your Target Date Range #

First, create a list of dates you want to report on. You can use a recursive CTE for dynamic ranges (like the last 7 days) or hardcode dates if you have a fixed set.

Example: Dynamic Date Range (Last 7 Days)

WITH date_range AS (
    SELECT CURRENT_DATE - INTERVAL 6 DAY AS report_date
    UNION ALL
    SELECT report_date + INTERVAL 1 DAY
    FROM date_range
    WHERE report_date < CURRENT_DATE
)

Example: Fixed Date Range

WITH date_range AS (
    SELECT '2024-05-01' AS report_date
    UNION ALL SELECT '2024-05-02'
    UNION ALL SELECT '2024-05-03'
    UNION ALL SELECT '2024-05-04'
)

Step 2: Pivot Data with Conditional Aggregation #

MySQL doesn't have a built-in PIVOT function, so we'll use conditional aggregation to turn rows into columns. This joins your worker data with the date range, then counts unbooked workers per date.

WITH date_range AS (
    -- Insert your date range CTE here (dynamic or fixed)
    SELECT CURRENT_DATE - INTERVAL 6 DAY AS report_date
    UNION ALL
    SELECT report_date + INTERVAL 1 DAY
    FROM date_range
    WHERE report_date < CURRENT_DATE
)
SELECT
    w.region,
    -- Count unbooked workers for each date in the range
    COUNT(CASE WHEN dr.report_date = CURRENT_DATE - INTERVAL 6 DAY AND cb.worker_id IS NULL THEN w.worker_id END) AS `2024-04-25_unbooked`,
    COUNT(CASE WHEN dr.report_date = CURRENT_DATE - INTERVAL 5 DAY AND cb.worker_id IS NULL THEN w.worker_id END) AS `2024-04-26_unbooked`,
    COUNT(CASE WHEN dr.report_date = CURRENT_DATE - INTERVAL 4 DAY AND cb.worker_id IS NULL THEN w.worker_id END) AS `2024-04-27_unbooked`,
    COUNT(CASE WHEN dr.report_date = CURRENT_DATE - INTERVAL 3 DAY AND cb.worker_id IS NULL THEN w.worker_id END) AS `2024-04-28_unbooked`,
    COUNT(CASE WHEN dr.report_date = CURRENT_DATE - INTERVAL 2 DAY AND cb.worker_id IS NULL THEN w.worker_id END) AS `2024-04-29_unbooked`,
    COUNT(CASE WHEN dr.report_date = CURRENT_DATE - INTERVAL 1 DAY AND cb.worker_id IS NULL THEN w.worker_id END) AS `2024-04-30_unbooked`,
    COUNT(CASE WHEN dr.report_date = CURRENT_DATE AND cb.worker_id IS NULL THEN w.worker_id END) AS `2024-05-01_unbooked`
FROM workers w
CROSS JOIN date_range dr
LEFT JOIN confirmed_bookings cb
    ON w.worker_id = cb.worker_id
    AND dr.report_date = cb.booking_date
    AND cb.status = 'confirmed' -- Filter to only confirmed bookings
GROUP BY w.region
ORDER BY w.region;

Key Details to Note #

• CROSS JOIN date_range: Ensures every worker is paired with every date in your range—this is critical for checking availability across all dates.
• LEFT JOIN confirmed_bookings: Links workers to their bookings on each date. If a worker has no booking that day, cb.worker_id will be NULL.
• COUNT(CASE...): For each date, we count workers where there's no matching confirmed booking. Backticks around column names handle dates as valid identifiers.

Need Fully Dynamic Columns? Use Prepared Statements #

If your date range changes frequently and you don't want to hardcode columns, use a prepared statement to auto-generate columns for each date:

SET @sql = NULL;
WITH date_range AS (
    SELECT CURRENT_DATE - INTERVAL 6 DAY AS report_date
    UNION ALL
    SELECT report_date + INTERVAL 1 DAY
    FROM date_range
    WHERE report_date < CURRENT_DATE
)
SELECT GROUP_CONCAT(
    DISTINCT CONCAT(
        'COUNT(CASE WHEN dr.report_date = ''', report_date, ''' AND cb.worker_id IS NULL THEN w.worker_id END) AS `', report_date, '_unbooked`'
    )
) INTO @sql
FROM date_range;

SET @sql = CONCAT(
    'SELECT w.region, ', @sql, ' 
     FROM workers w
     CROSS JOIN date_range dr
     LEFT JOIN confirmed_bookings cb
         ON w.worker_id = cb.worker_id
         AND dr.report_date = cb.booking_date
         AND cb.status = ''confirmed''
     GROUP BY w.region
     ORDER BY w.region;'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

This will automatically create a column for every date in your range, no manual updates needed.

Quick Adjustments for Your Schema #

• If you don't need to group by region, remove the region column from the SELECT and GROUP BY clauses.
• Tweak the status filter in the LEFT JOIN to match your actual confirmed booking logic (e.g., if your table uses a boolean is_confirmed instead of a status string).

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