我有一个表重复本身的问题。我想在同一张桌子上letter
显示一次letter_comments
。而不是letter
每个都重复letter_comments
Letter
ID | TEXT |
1 My First Letter
Letter Comments
ID | LETTER_ID | TEXT |
3 1 Wow great one
4 1 This story is awesome
我当前的查询:
SELECT Json_object('id', l.id, 'text', l.text) AS letter,
Json_object('text', c.text) AS letter_comments
FROM letter_comments c
LEFT JOIN letter l
ON l.id = c.letter_id
当前结果:
{letter: {id: 1, text: "My First Letter"}, letter_comments: {"Wow great one"}, letter: {id: 1, text: "My First Letter"}, letter_comments: {"This story is awesome"}}
预期结果
letter: {id: 1, text: "My First Letter"}, letter_comments: {{"Wow great one"}, {"This story is awesome"}}
使用JSON_ARRAYAGG()
所有对象结合起来,在一个阵列相同的字母。
另外,您的表顺序错误LEFT JOIN
。在LEFT JOIN
父表中排在最前面,然后是子表。
SELECT JSON_OBJECT('id', l.id, 'text', l.text',
'letter_comments', JSON_ARRAYAGG(c.text)) AS letter
FROM letter AS l
LEFT JOIN letter_comments AS c ON l.id = c.letter_id
GROUP BY l.id
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句