MySQL仅为不同的行选择值的总和，并用GROUP BY填充内部联接产生的缺失行-解网

问：

对于我的包机预订系统，我正在实现一个表来存储可能的旅行和一个已完成的预订表。tourbooking

让我简化它，只是为了提出一个更简单的问题。提出以下建议。

该表具有时间戳和整数，表示可能的最大预订数。tourstartsseats

该表具有 varchar 名称的外键和预留席位数的整数。bookingtourtour.idclientpax

我用随机值填充表格，例如......

  INSERT INTO booking (tour,client,pax) VALUES 
    (FLOOR(20000 + (RAND() * 100)), MD5(RAND()), FLOOR(1 + (RAND() * 5) ));

查看此 DB Fiddle https://www.db-fiddle.com/f/wmwkZ4JSj6geH6TVS4Tsxr/2

设置完成后，我就可以获得给定日期的每次旅行的预订

SELECT tour.id, count(booking.id) as parties, sum(booking.pax) as pax 
    FROM booking INNER JOIN tour ON tour.id = booking.tour 
    WHERE DATE(tour.starts)='2023-12-01'
    GROUP BY tour.id;

或一系列日期

SELECT tour.id, DATE(tour.starts) AS date, COUNT(booking.id) AS parties, SUM(booking.pax) AS pax 
  FROM booking INNER JOIN tour ON tour.id = booking.tour 
  WHERE date(tour.starts) BETWEEN '2023-12-01' AND '2023-12-14'
  GROUP BY tour.id
  ORDER BY tour.starts;

目前为止，一切都好。

我需要建立一个可用性日历，所以我想分组，不是按巡演，而是按日期了解每天的可用性

SELECT DATE(tour.starts) AS date, COUNT(DISTINCT tour.id) as tours, COUNT(booking.id) AS parties, 
SUM(booking.pax) pax, SUM(tour.seats) - SUM(booking.pax) AS available 
  FROM booking INNER JOIN tour ON tour.id = booking.tour 
  WHERE DATE(tour.starts) BETWEEN '2023-12-01' AND '2023-12-14'
  GROUP BY DATE(tour.starts)
  ORDER BY date;

但是这个查询有两个问题

问题 1：可用性计算不正确，因为它会为每次旅行团的预订数量添加所有 tour.seats。我只想把不同旅行的座位相加。就像我发明的新语句 SUM（tour.seats FOR DISTINCT tour.id）。

问题 2：没有任何预订的旅行团不会出现。如果一个日期只预订了一个旅行团，其他旅行团不会将他们的座位总和为可用（假设我们解决了问题#1）。如果某个日期没有预订，则该日期根本不会出现。但是，有旅游和座位可用。

关于如何解决这个问题的任何想法？

mysql 选择分组

SELECT
  tour.id AS id,
  DATE(tour.starts) AS date,
  IFNULL(COUNT(booking.id), 0) AS parties, 
  IFNULL(SUM(booking.pax), 0) AS pax,
  tour.seats - IFNULL(SUM(booking.pax), 0) AS available 
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

然后将其包装在外部选择中，以按日期获取摘要。注意：我添加了该功能，以便超额预订的旅行不会减少预订不足的旅行的可用性。GREATEST

  SELECT
    date,
    COUNT(id) AS tours,
    SUM(parties) AS parties,
    SUM(pax) AS pax,
    SUM(GREATEST(available, 0)) AS available
  FROM (
    SELECT
      tour.id AS id,
      DATE(tour.starts) AS date,
      IFNULL(COUNT(booking.id), 0) AS parties, 
      IFNULL(SUM(booking.pax), 0) AS pax,
      tour.seats - IFNULL(SUM(booking.pax), 0) AS available 
    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
  ) t
  GROUP BY t.date
  ORDER BY t.date

MySQL仅为不同的行选择值的总和，并用GROUP BY填充内部联接产生的缺失行

mysql select sum of values just for distinct rows and filling missing rows resulting from an inner join with group by

评论

评论