|
|
我需要trophies赢得一支球队，每一个奖杯都是在特定赛季的比赛中获得的，但一支球队可以在不同赛季的同一场比赛中获得奖杯。$ z- A, T4 L7 K) S" l+ f
我写了一个查询，从数据库中提取奖杯：9 V5 P" z, a9 G1 z& M
$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 ");本质上，我trophies所有的球员都是从表中选的，team_trophies并加入了competition_seasons获得奖杯的赛季细节competition表中相同。
% g# n. @: I/ q3 }- o有效，我得到：! v: F; c( ]9 s+ J6 L$ g, U. ~
"team_id": "23291", "season_id": "2", "position": "Winner", "wins": "4", "id": "1093", "competition_id": "1093", "name": " remier League", "update_at": "2018-06-04 12:12:30", "country_id": "1", "category": "1", "season_name": "2017", "competition_name": "remier League" "team_id": "23291", "season_id": "3", "position": "Runner-up", "wins": "1", "id": "1093", "competition_id": "1093", "name": "remier League", "update_at": "2018-06-04 12:14:39", "country_id": "1", "category": "1", "season_name": "2015", "competition_name": "remier League" }但我会回到这样的结果:) P2 O; @: w" Q- Y
"team_id": "23291", "position": "Winner", "wins": "4", "id": "1093", "competition_id": "1093", "name": "remier 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": "remier League" }如你所见，我的结果包括一行seasons奖杯的array，这样更可读，避免重复。
% b v! ^, p* e' n% b! q可以用来做到这一点sql还是我需要解决php？/ |* U. r5 k$ O( C! k
谢谢。9 t# b, i0 m; r: ^; p1 p5 S
更新-表结构+ t5 ^* X) r$ V, K
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 INSERTsINSERT INTO `team_trophies` (`team_id`,`season_id`,`position`,`wins` VALUES (23291,2,'Winner四、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`) VALUES2018年，1093，14963，-06-2018年，04 12:10:28)-06-04 12:12:30)
. C% S) I; }; n8 s5 L x 解决方案: |
|
技术问答
176 人阅读
|
0 人回复
|
2023-09-12
