我有一個tasks看起來像這樣的表：
userId caption status id
1 Paul done 1
2 Ali notDone 18
3 Kevin notDone 12
3 Elisa notDone 13
我將它與另一個表連接起來users以查找taskswhere的數量status = notDone。我這樣做：
SELECT u.id,
t.number_of_tasks,
FROM users u
INNER JOIN (
SELECT userId, COUNT(*) number_of_tasks
FROM tasks
WHERE status = "notDone"
GROUP BY userId
) t ON u.id = t.userId
"""
現在，我想創建另一個列captions，該列以某種方式包含所有captions包含在count并滿足 join where 條件的串列。
例如，我希望這是行之一。我怎樣才能做到這一點？
userId number_of_tasks captions
3 2 ["Kevin", "Elisa"]
uj5u.com熱心網友回復：
您可以json_group_array()在子查詢中使用聚合函式為每個用戶創建標題串列：
SELECT u.id, t.number_of_tasks, t.captions
FROM users u
INNER JOIN (
SELECT userId,
COUNT(*) number_of_tasks,
json_group_array(caption) captions
FROM tasks
WHERE status = 'notDone'
GROUP BY userId
) t ON u.id = t.userId;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/459475.html