如何在一個查詢中處理可能為空元素的嵌套分組

正如您在這個 sql-fiddle中看到的，我有三個表：events、event_dates和event_times.

原始表包含各種其他欄位，因為它們不相關，所以我省略了它們，我只需要在最終結果中包含它們，但我不對它們進行操作/排序/過濾/分組。

一個事件有1+ event_dates和一個 event_date 有 0+ event_times。

從對象的角度來看，事件對象具有 event_date 對象的列表，而後者又可以具有 event_time 對象的列表。

我的最終目標是獲得一組事件對象。

我的計劃是設計一個查詢，返回匹配記錄的 id，並從這些結果中以 3 個查詢從他們的表中獲取記錄，並在程式碼中組裝所有內容（這是必要的，因為 SQL 範圍之外的各種數據轉換）。

我的問題是讓第一個查詢正確

目前我有以下查詢

SELECT events.id,
CONCAT('[', GROUP_CONCAT(event_dates.id SEPARATOR ','), ']') AS event_date_ids,
CONCAT('[', COALESCE(GROUP_CONCAT(event_times_per_event_date.event_time_ids SEPARATOR ','), '[]'), ']') AS event_time_ids
FROM events
INNER JOIN event_dates ON event_dates.event_id = events.id
LEFT JOIN (
 SELECT date_id AS event_date_id, CONCAT('[', COALESCE(GROUP_CONCAT(event_times.id SEPARATOR ','), ''), ']') AS event_time_ids
 FROM event_dates
 LEFT JOIN event_times ON event_times.date_id = event_dates.id
 GROUP BY event_dates.id
) AS event_times_per_event_date ON event_times_per_event_date.event_date_id = event_dates.id
GROUP BY events.id

在以下情況下失敗：

給定事件e具有三個日期d1、d2和d3，其中d1有兩次（t1,t2），d2有零次，d3有兩次（t3,t4）。查詢的結果將是：

event_id  |  event_date_ids  |  event_time_ids
1         |  [1,2,3]         |  [[1,2],[3,4]] 

我期望 event_time_ids 中有一個空數組，這樣我就可以將 event_date_ids 中的數組索引與 event_time_ids 中的數組索引匹配：

event_id  |  event_date_ids  |  event_time_ids
1         |  [1,2,3]         |  [[1,2], [], [3,4]] 

我怎樣才能得到這個？

PS：如果我的問題混亂或不清楚，我深表歉意，我準備提供所有其他必要的資訊。

謝謝！

更新 1

非常感謝@Andriy M 發現了我的錯誤。

繼續前進，我必須添加event_categories事件具有**1+**類別的等式。

DISTINCT在中添加 aGROUP_CONCAT會破壞我目前的解決方案，因為空數組將被刪除。解決方案是將這個查詢變成一個子查詢，並將其結果連接到一個查詢中，該查詢獲取所有其他事件數據並按events.id. event_time_ids 對於每個事件行都是重複的，因此為該列選擇任何行都可以。

您需要對您的查詢應用一個小的更改，這在下面強調：

SELECT events.id,
CONCAT('[', GROUP_CONCAT(event_dates.id SEPARATOR ','), ']') AS event_date_ids,
CONCAT('[', COALESCE(GROUP_CONCAT(event_times_per_event_date.event_time_ids SEPARATOR ','), '[]'), ']') AS event_time_ids
FROM events
INNER JOIN event_dates ON event_dates.event_id = events.id
LEFT JOIN (
 SELECT **event_dates.id** AS event_date_id, CONCAT('[', COALESCE(GROUP_CONCAT(event_times.id SEPARATOR ','), ''), ']') AS event_time_ids
 FROM event_dates
 LEFT JOIN event_times ON event_times.date_id = event_dates.id
 GROUP BY event_dates.id
) AS event_times_per_event_date ON event_times_per_event_date.event_date_id = event_dates.id
GROUP BY events.id;

在派生表中，您正確分組，但取而代之的是event_dates.id檢索。event_times.date_id這是一個錯誤，因為當沒有匹配時，它是空的。正是因為一個值作為 null 返回，所以外部查詢省略了相應的空組。替換event_times.date_id AS event_date_id為event_dates.id AS event_date_id修復問題。

引用自：https://dba.stackexchange.com/questions/132880