php - 使用 UNION 进行三个查询

Question

我正在尝试使用 MYSQL 的 UNION 函数将三个单独的查询合并为一个。

我想输出一张包含booking_count、reserve_count和cancel_count的数据表。有些行将具有所有三项的值，有些行将仅具有一项的值。

不知道是否有人可以指出我哪里出错了？

我遇到的错误是:

注意:未定义索引:cancel_count注意:未定义索引:reserve_count

我认为这是 MYSQL 查询的问题，因为 PHP 看起来很好

$query = "SELECT id, class_date, class_id, COUNT(*) AS booked_count   
          FROM bookings
          WHERE booking_status='#BOOKED#'
          UNION ALL

          SELECT id, class_date, class_id, COUNT(*) AS reserve_count  
          FROM bookings
          WHERE booking_status='#RESERVE#'
          UNION ALL

      SELECT id, class_date, class_id, COUNT(*) AS cancel_count  
          FROM bookings
          WHERE booking_status='#CANCELLED#'
          GROUP BY class_date, class_id
          ORDER BY class_date ASC, class_id ASC" ;

$result = mysqli_query($sql,$query);

while($row = mysqli_fetch_assoc($result)) {
   $union[$row['id']] = array('class_date' => $row['class_date'], 'class_id' => $row['class_id'], 'booked_count' => $row['booked_count'], 'reserve_count' => $row['reserve_count'], 'cancel_count' => $row['cancel_count']);
}

echo '<table><tr><th>id</th><th>class_id</th><th>class_date</th><th>booking_number</th><th>reserve_number</th><th>cancelled_number</th></tr>';

foreach($union as $union) {
   echo '<tr>';
   echo '<td>&nbsp;</td>'; // id
   echo '<td>'.$union['class_id'].'</td>';
   echo '<td>'.$union['class_date'].'</td>';
   echo '<td>'.$union['booked_count'].'</td>';
   echo '<td>'.$union['reserve_count'].'</td>';
   echo '<td>'.$union['cancel_count'].'</td>';
   echo '</tr>';
}

echo '</table>';

Answer 1

我认为这样的事情会起作用:

SELECT id, class_date, class_id,
  SUM(booked_count) AS booked_count,
  SUM(reserve_count) AS reserve_count,
  SUM(cancel_count) AS cancel_count
FROM
(SELECT id, class_date, class_id,
  1 AS booked_count,
  0 AS reserve_count,
  0 AS cancel_count 
FROM bookings
WHERE booking_status = '#BOOKED#'
UNION ALL
SELECT id, class_date, class_id,
  0 AS booked_count,
  1 AS reserve_count,
  0 AS cancel_count 
FROM bookings
WHERE booking_status = '#RESERVE#'
UNION ALL
SELECT id, class_date, class_id,
  0 AS booked_count,
  0 AS reserve_count,
  1 AS cancel_count  
FROM bookings
WHERE booking_status = '#CANCELLED#') AS t
GROUP BY class_date, class_id