MySQL按日期与地点分组统计预约数：实现行列转换查询需求

Hey there! Let's figure out how to turn your row-based appointment results into that clean columnar view where dates act as headers and locations show their daily appointment counts.

First, let's recap: your current query returns each location-date pair as a separate row, which is great for raw data but not the formatted view you're after. To switch this to a pivot table structure, we'll use either conditional aggregation (for fixed, known dates) or dynamic SQL (for when dates change automatically).

Option 1: Fixed Dates (Works for All SQL Databases) #

If you know exactly which dates you need to display, conditional aggregation is the most straightforward, universal approach. We'll use CASE statements to count appointments per date, then group by location.

Here's the adjusted query tailored to your sample data:

SELECT 
    t2.name AS location_name,
    COUNT(CASE WHEN t1.start_datetime = '2021-06-21' THEN 1 END) AS `2021-06-21`,
    -- Add more CASE lines here for additional dates, e.g.:
    -- COUNT(CASE WHEN t1.start_datetime = '2021-06-22' THEN 1 END) AS `2021-06-22`
FROM appointment t1 
INNER JOIN location t2 ON t1.location_id = t2.id 
GROUP BY t2.name;

Sample Output #

This works because the CASE statement only counts rows where the date matches, returning NULL otherwise (and COUNT ignores NULL values). If you want to include locations with zero appointments across all dates, swap INNER JOIN with LEFT JOIN to retain all entries from the location table.

Option 2: Dynamic Dates (Adjusts Automatically) #

If your appointment dates change frequently and you don't want to update the query manually, use dynamic SQL to generate date columns on the fly. Syntax varies slightly by database:

MySQL/MariaDB #

-- Generate date column definitions
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'COUNT(CASE WHEN start_datetime = ''',
      start_datetime,
      ''' THEN 1 END) AS `',
      start_datetime,
      '`'
    )
  ) INTO @sql
FROM appointment;

-- Build and execute the full query
SET @sql = CONCAT('SELECT t2.name AS location_name, ', @sql, ' 
                  FROM appointment t1 
                  INNER JOIN location t2 ON t1.location_id = t2.id 
                  GROUP BY t2.name');

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

PostgreSQL #

DO $$
DECLARE
  date_columns text;
BEGIN
  -- Generate date column logic
  SELECT string_agg(DISTINCT
    format('COUNT(CASE WHEN start_datetime = %L THEN 1 END) AS %I',
           start_datetime, start_datetime),
    ', ')
  INTO date_columns
  FROM appointment;

  -- Execute dynamic query
  EXECUTE format('
    SELECT t2.name AS location_name, %s
    FROM appointment t1
    INNER JOIN location t2 ON t1.location_id = t2.id
    GROUP BY t2.name', date_columns);
END $$;

SQL Server #

SQL Server has a built-in PIVOT operator to simplify this:

DECLARE @date_columns AS NVARCHAR(MAX),
        @pivot_query  AS NVARCHAR(MAX);

-- Generate list of date columns
SELECT @date_columns = STUFF((SELECT ',' + QUOTENAME(start_datetime)
                    FROM appointment
                    GROUP BY start_datetime
                    ORDER BY start_datetime
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),1,1,'');

-- Build and run the pivot query
SET @pivot_query = N'SELECT location_name, ' + @date_columns + N' 
            FROM (
                SELECT t2.name AS location_name, t1.start_datetime, 1 AS appointment
                FROM appointment t1 
                INNER JOIN location t2 ON t1.location_id = t2.id
            ) AS source_data
            PIVOT (
                COUNT(appointment)
                FOR start_datetime IN (' + @date_columns + N')
            ) AS pivot_table';

EXEC sp_executesql @pivot_query;

Quick Tip #

Want friendlier date headers? Modify the column name logic in dynamic queries with date formatting functions:

• MySQL: Use DATE_FORMAT(start_datetime, '%M %d, %Y')
• PostgreSQL: Use TO_CHAR(start_datetime, 'FMMonth DD, YYYY')
• SQL Server: Use FORMAT(start_datetime, 'MMMM dd, yyyy')

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