如何通过3个多对多关系表将数组与MySQL连接

如何通过3个多对多关系表将数组与MySQL连接

“简便”方法是使用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