MySQL查询：按日期统计可用座位并补全缺失日期

核心思路 #

1. 先按单个旅游团统计预订数据，避免因多笔预订导致座位数重复计算
2. 使用LEFT JOIN确保无预订的旅游团被纳入统计
3. 如需显示日期范围内的所有日期（含无旅游团的日期），需生成日期序列并关联统计结果

基础版：统计有旅游团的日期（含无预订的团） #

这个查询直接解决你提到的两个核心问题：

SELECT
  tour_date AS date,
  COUNT(*) AS tours,
  SUM(parties) AS parties,
  SUM(booked_pax) AS pax,
  SUM(seats) - SUM(booked_pax) AS available
FROM (
  -- 子查询：统计每个旅游团的预订情况，无预订的团显示0
  SELECT 
    tour.id,
    DATE(tour.starts) AS tour_date,
    tour.seats,
    COUNT(booking.id) AS parties,
    COALESCE(SUM(booking.pax), 0) AS booked_pax
  FROM tour
  LEFT JOIN booking ON tour.id = booking.tour
  WHERE DATE(tour.starts) BETWEEN '2023-12-01' AND '2023-12-14'
  GROUP BY tour.id, tour_date, tour.seats
) AS tour_booking_stats
GROUP BY tour_date
ORDER BY tour_date;

关键修复点 #

• 问题1解决：子查询先按tour.id分组，每个旅游团的seats只被计算一次，外层聚合时不会出现重复求和的情况
• 问题2解决：用LEFT JOIN从tour表关联booking表，无预订的旅游团会被保留，其parties和booked_pax通过COALESCE函数设为0，确保统计完整

进阶版：显示日期范围内的所有日期（含无旅游团的日期） #

如果需要日历完整展示指定范围的所有日期（哪怕当天没有任何旅游团），可通过递归CTE生成日期序列：

-- 生成指定日期范围内的所有日期
WITH RECURSIVE date_range AS (
  SELECT '2023-12-01' AS date
  UNION ALL
  SELECT DATE_ADD(date, INTERVAL 1 DAY)
  FROM date_range
  WHERE date < '2023-12-14'
)
SELECT
  dr.date,
  COALESCE(tbs.tours, 0) AS tours,
  COALESCE(tbs.parties, 0) AS parties,
  COALESCE(tbs.pax, 0) AS pax,
  COALESCE(tbs.total_seats - tbs.pax, 0) AS available
FROM date_range dr
LEFT JOIN (
  -- 按日期聚合旅游团和预订数据
  SELECT
    tour_date,
    COUNT(*) AS tours,
    SUM(parties) AS parties,
    SUM(booked_pax) AS pax,
    SUM(seats) AS total_seats
  FROM (
    SELECT 
      tour.id,
      DATE(tour.starts) AS tour_date,
      tour.seats,
      COUNT(booking.id) AS parties,
      COALESCE(SUM(booking.pax), 0) AS booked_pax
    FROM tour
    LEFT JOIN booking ON tour.id = booking.tour
    WHERE DATE(tour.starts) BETWEEN '2023-12-01' AND '2023-12-14'
    GROUP BY tour.id, tour_date, tour.seats
  ) AS tour_booking_stats
  GROUP BY tour_date
) AS tbs ON dr.date = tbs.tour_date
ORDER BY dr.date;

补充说明 #

• 递归CTE适用于MySQL 8.0+、PostgreSQL、SQL Server等支持CTE的数据库
• COALESCE函数用于将NULL值替换为0，保证统计数据的一致性和可读性

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