作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我需要获得一支球队赢得的奖杯
,每个奖杯都是在特定赛季的比赛中获得的,但是一支球队可以在不同赛季的同一场比赛中赢得一座奖杯。
我写了一个从数据库中提取奖杯的查询:
$sql = $this->db->prepare("SELECT t.*, s.*, c.*,
t.team_id as team_id,
s.id as season_id,
s.name as season_name,
c.id as competition_id,
c.name as competition_name
FROM team_trophies t
INNER JOIN competition_seasons s ON s.id = t.season_id
INNER JOIN competition c ON c.id = s.competition_id
WHERE team_id = :team_id
");
本质上,我从表 team_trophies
中为特定团队选择了所有 trophies
并加入了 competition_seasons
以检索奖杯的赛季详细信息, competition
表也是如此。
这有效,我得到:
[
{
"team_id": "23291",
"season_id": "2",
"position": "Winner",
"wins": "4",
"id": "1093",
"competition_id": "1093",
"name": "Premier League",
"update_at": "2018-06-04 12:12:30",
"country_id": "1",
"category": "1",
"season_name": "2017",
"competition_name": "Premier League"
},
{
"team_id": "23291",
"season_id": "3",
"position": "Runner-up",
"wins": "1",
"id": "1093",
"competition_id": "1093",
"name": "Premier League",
"update_at": "2018-06-04 12:14:39",
"country_id": "1",
"category": "1",
"season_name": "2015",
"competition_name": "Premier League"
}
]
但我会返回这样的结果:
[
{
"team_id": "23291",
"position": "Winner",
"wins": "4",
"id": "1093",
"competition_id": "1093",
"name": "Premier League",
"update_at": "2018-06-04 12:12:30",
"country_id": "1",
"category": "1",
"seasons": [
["season_name":"2017", "season_id":"2"],
["season_name":"2015", "season_id":"3"],
]
"competition_name": "Premier League"
}
]
如您所见,我在结果中有一行以 数组
形式包含该奖杯的 seasons
,这样可读性更好并避免冗余。
是否可以使用 sql
实现此目的?或者我需要使用 php
来解决问题?
谢谢。
更新 - 表结构
CREATE TABLE IF NOT EXISTS `swp`.`competition` (
`id` INT NOT NULL,
`name` VARCHAR(255) NULL,
PRIMARY KEY (`id`),
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `swp`.`competition_seasons` (
`id` INT NOT NULL AUTO_INCREMENT,
`competition_id` INT NOT NULL,
`season_id` INT NULL,
`name` VARCHAR(45) NOT NULL,
`update_at` DATETIME NULL,
PRIMARY KEY (`id`),
INDEX `FK_competition_competition_seasons_competition_id_idx` (`competition_id` ASC),
CONSTRAINT `FK_competition_competition_seasons_competition_id`
FOREIGN KEY (`competition_id`)
REFERENCES `swp`.`competition` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `swp`.`team_trophies` (
`team_id` INT NOT NULL,
`season_id` INT NOT NULL,
`position` VARCHAR(255) NOT NULL,
`wins` INT NOT NULL,
INDEX `FK_team_team_trophies_team_id_idx` (`team_id` ASC),
INDEX `FK_season_team_trophies_season_id_idx` (`season_id` ASC),
CONSTRAINT `FK_team_team_trophies_team_id`
FOREIGN KEY (`team_id`)
REFERENCES `swp`.`team` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `FK_season_team_trophies_season_id`
FOREIGN KEY (`season_id`)
REFERENCES `swp`.`competition_seasons` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- Some INSERTs
INSERT INTO `team_trophies` (`team_id`, `season_id`, `position`, `wins` VALUES (23291, 2, 'Winner', 4), (23291, 3, 'Runner-up', 1);
INSERT INTO `competition` (`id`, `country_id`, `name`, `category`) VALUES (1093, 1, 'Premier League', 1);
INSERT INTO `competition_seasons` (`id`, `competition_id`, `season_id`,
`name`, `update_at`) VALUES
(1, 1093, 14963, '2018', '2018-06-04 12:10:28'),
(2, 1093, 13198, '2017', '2018-06-04 12:12:30');
最佳答案
由一位名叫 Ghost 的大师提供,这是一个将一维数组转换为多维数组的简单示例...
<?php
$my_array = array();
$my_array =
array
(
0 => array
(
id => '4',
project_id => '2289',
task => 'Drawing'
),
1 => array
(
id => '5',
project_id => '2289',
task => 'Surveying'
),
2 => array
(
id => '6',
project_id => '2289',
task => 'Meeting'
),
3 => array
(
id => '1',
project_id => '2282',
task => 'Folding'
),
4 => array
(
id => '2',
project_id => '2282',
task => 'Printing'
),
5 => array
(
id => '3',
project_id => '2282',
task => 'Cutting'
)
);
$new_array = array();
foreach ($my_array as $row) {
$new_array[$row['project_id']]['project_id'] = $row['project_id'];
$new_array[$row['project_id']]['task'][] = $row['task'];
}
$new_array = array_values($new_array); // reindex
// removes `$row['project_id']` on each group
print_r($new_array);
?>
输出:
Array
(
[0] => Array
(
[project_id] => 2289
[task] => Array
(
[0] => Drawing
[1] => Surveying
[2] => Meeting
)
)
[1] => Array
(
[project_id] => 2282
[task] => Array
(
[0] => Folding
[1] => Printing
[2] => Cutting
)
)
)
关于mysql - 如何对数组中的特定字段进行分组?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/50680400/
我是一名优秀的程序员,十分优秀!