问题描述
我在本地服务器上使用phpMyAdmin创建了一个mySQL数据库.在这个数据库中,我存储了我的朋友的名字和最喜欢的NBA球队,这显然是多对多的关系.因此,我在MySQL中运行以下脚本来为此数据库创建适当的表:
I created a mySQL database with phpMyAdmin in my local server. In this database I store the names and the favourite NBA teams of my friends.This is obviously a many-to-many relationship. For this reason, I run the followed script in MySQL to create the appropriate tables for this database:
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
);
很显然,我在这些表中插入了一些值,但是为了节省空间,在这里我没有广泛提供源代码.一小部分内容如下:
Obviously, I inserted some values to these tables but I do not provide extensively the source code here so as to save some space. An small piece of it is the following:
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输出:
After running a PHP script that fetches the data from the database and print them, I want to have the following kind of valid json output for each of my friends:
{
"id": "1",
"name": "Alex James",
"Current team": ["Boston Celtics"]
"Past team": [ "Cleveland Cavaliers", "Houston Rockets"]
}
我怎样才能使每个使用MySQL的人都喜欢的团队呢?
How can I make this array of favourite teams for each person with MySQL?
P.S.我当前的问题是受以下已回答问题的启发:
P.S. My current question is inspired by the following answered question: How to join arrays with MySQL from 3 tables of many-to-many relationship
推荐答案
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
这篇关于如何根据联接数组的值为INNER JOIN表选择列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!