我得到了两个表：所有者和存储如下：
表：所有者
id | owner | box
----------------------------
1 | Nick | []
2 | Jack | []
3 | Jane | []
表：存储
id | fruit | owner_id
---------------------------
1 | Apple | 1
2 | Apple | 3
3 | Banana | 2
4 | Banana | 1
5 | Banana | 1
6 | orange | 1
7 | Apple | 3
8 | Orange | 2
9 | Apple | 2
问题：是否有查询结果中每个主人的盒子里有多少水果喜欢：
Nick:[Apple:1,Banan:2,Orange:1]
Jack:[Apple:1,Banan:1,Orange:1]
Jane:[Apple:2,Banan:0,Orange:0]
我试图连接两个表，但它返回了数百条重复的记录。如果有人能带我出去，我将不胜感激。
SELECT owners.id,
JSON_OBJECTAGG(fruits.fruit, COALESCE(boxes.cnt, 0)) boxes
FROM (SELECT DISTINCT fruit
FROM Storage) fruits
CROSS JOIN owners
LEFT JOIN (SELECT owners.id, Storage.fruit, COUNT(*) cnt
FROM owners
JOIN Storage ON owners.id = Storage.owner_id
GROUP BY owners.id, Storage.fruit) boxes ON owners.id = boxes.id
AND fruits.fruit = boxes.fruit
GROUP BY owners.id;
小提琴
SELECT owner, GROUP_CONCAT(CONCAT('(', fruit,',',c,')') SEPARATOR ';') as counts
FROM
(SELECT owner, owner_id, fruit, COUNT(*) as c FROM `storage`
JOIN owners ON owners.id = storage.owner_id
GROUP BY fruit, owner_id) as t
GROUP BY owner_id
这里的关键是括号中的请求
SELECT owner_id, fruit, COUNT(*) as c FROM `storage` GROUP BY fruit, owner_id
它提供了类似的东西
owner_id | fruit | c
---------------------
1 | apple | 1
1 | banana | 2
1 | orange | 1
你只需要用水果和c柱进行组concat