“简便”方法是使用CONCAT生成JSON。
并使用GROUP_CONCAT将多个团队记录合并到一个JSON数组中。
此方法也可在不支持创建JSON函数的旧MySQL版本中使用。
询问
SET SESSION group_concat_max_len = @@max_allowed_packetSELECt CONCAt( "{" , '"id"' , ":" , '"' , friends.id , '"' , "," , '"name"' , ":" , '"' , friends.name , '"' , "," , '"team"' , ":" , "[" , GROUP_CONCAt('"', teams.name, '"') , "]" , "}" ) AS jsonFROM friends INNER JOIN relations ON friends.id = relations.friends_idINNER JOIN teams ON relations.teams_id = teams.idWHERe friends.id = 1结果
| json ||-----------------------------------------------------------------|| {"id":"1","name":"David Belton","team":["Cleveland Cavaliers"]} |演示
http://www.sqlfiddle.com/#!9/4cd244/19
编辑了更多朋友
询问
SET SESSION group_concat_max_len = @@max_allowed_packetSELECt CONCAt( "[" , GROUP_CONCAt(json_records.json) # combine json records into a string , "]" ) AS jsonFROM ( SELECt CONCAt( "{" , '"id"' , ":" , '"' , friends.id , '"' , "," , '"name"' , ":" , '"' , friends.name , '"' , "," , '"team"' , ":" , "[" , GROUP_CONCAt('"', teams.name, '"') , "]" , "}" ) AS json FROM friends INNER JOIN relations ON friends.id = relations.friends_id INNER JOIN teams ON relations.teams_id = teams.id WHERe friends.id IN(SELECt id FROM friends) #select the friends you need or just simply friends.id IN(1, 2) GROUP BY friends.id) AS json_records结果
| json ||--------------------------------------------------------------------------------------------------------------------------------------------------|| [{"id":"1","name":"David Belton","team":["Cleveland Cavaliers"]},{"id":"2","name":"Alex James","team":["Boston Celtics","Cleveland Cavaliers"]}] |演示
http://www.sqlfiddle.com/#!9/4cd244/61
