如何根据联接数组的值为内部联接表选择列

Question

我在本地服务器中使用phpMyAdmin创建了一个phpMyAdmin数据库。在这个数据库中，我存储了我的friends.This的名字和最喜欢的NBA球队，这显然是一种多到多的关系。出于这个原因，我在MySQL中运行了下面的脚本来为这个数据库创建适当的表：

CREATE TABLE `friends` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `teams` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `relations` (
  `friends_id` int(4) NOT NULL,
  `teams_id` int(4) NOT NULL,
  `status` varchar(30) NOT NULL
);

显然，我在这些表中插入了一些值，但为了节省一些空间，我并没有在这里提供大量的源代码。其中一小部分如下：

INSERT INTO `friends` (`id`, `name`)
VALUES
    (1,'David Belton'),
    (2,'Alex James');

INSERT INTO `teams` (`id`, `name`)
VALUES
    (1,'Cleveland Cavaliers'),
    (2,'Boston Celtics'),
    (3,'Houston Rockets');

INSERT INTO `relations` (`friends_id`, `teams_id`, `status`)
VALUES
    (1,1, 'Current'),
    (2,1, 'Current'),
    (2,2, 'Past'),
    (2,3, 'Past');

在运行了从数据库获取数据并打印数据的PHP脚本之后，我希望为我的每个朋友提供以下类型的有效的json输出：

{
    "id": "1",
    "name": "Alex James",
    "Current team": ["Boston Celtics"]
    "Past team": [ "Cleveland Cavaliers", "Houston Rockets"] 
}

我怎样才能为MySQL的每个人打造一组最喜欢的团队呢？

P.S.我目前的问题是由以下问题引起的：How to join arrays with MySQL from 3 tables of many-to-many relationship

Answer 1

SELECT 
   CONCAT( 
    "{"
   ,     '"id"' , ":" , '"' , friends.id , '"' , ","
   ,     '"name"' , ":" , '"' , friends.name , '"' , ","
    , 
   CASE 
   WHEN relations.status = 'Current' 
     THEN CONCAT('"CurrentTeam":["',    teams.name ,'"]')
   ELSE CONCAT('"pastTeam": '   ,   '[' ,   GROUP_CONCAT( '"',teams.name, '"'),']'  )
     END   
   , "}"
   )
  AS json
FROM 
 friends 
INNER JOIN 
 relations 
ON 
 friends.id = relations.friends_id
INNER JOIN
 teams 
ON
relations.teams_id = teams.id
 group by friends.id,relations.status

http://sqlfiddle.com/#!9/694bc69/23

Answer 2

对于这些类型的查询，您必须使用mysql版本5.7，使用这些版本，您可以在查询中使用json类型和函数。

答：

SELECT GROUP_CONCAT( JSON_OBJECT(
'id', f.id,  'fname', f.name,  'tname', t.name,  'current_status', r.status), (SELECT JSON_OBJECT('name', tm.name)
FROM teams tm, relations re
WHERE tm.id = re.teams_id
AND f.id = re.friends_id
AND re.status =  "Past"))
FROM teams t, relations r, friends f
WHERE t.id = r.teams_id
AND f.id = r.friends_id
AND r.status =  "Current"