时间戳

首页 > 解决方案 > 无法在一行中获取一个 ID 和另一个同名 ID？

php - 无法在一行中获取一个 ID 和另一个同名 ID？

问题描述

我无法让教练连续输入运动员姓名

SELECT dev_test_team.team_id, dev_test_team.team_name,
dev_test_team.user_id, `dev_user`.`user_id` as `athlete_id`, 
`dev_user`.`user_type` FROM `dev_test_team`
CROSS JOIN `dev_user`
INNER JOIN `dev_test_teams_athlete` as `tt` ON `dev_user`.`user_id` = `tt`.`user_id` GROUP BY dev_test_team.team_id, dev_test_team.team_name, dev_test_team.user_id

这是在查询中使用Cross join 关键字时的结果

我的 dev_test_team 表是：

在此处输入图像描述

我的 dev_test_teams_athlete 表是：

在此处输入图像描述

我的 dev_user 表是：

在此处输入图像描述

最后，我想要这样的结果，如表所示

----------------------------------------------------------------------------
 |first_name(coach_name)| first_name(athlete_name) | team_id  | team_name
 ---------------------------------------------------------------------------
        coach1                  athlete1                 1        ind
        coach2                  athlete2                 2        usa
        coach3                  athlete3                 3        uk
        coach3                  athlete4                 3        uk

标签： phpmysqljoin

解决方案

你可以尝试上桌两次JOIN。dev_user

一张dev_test_teams_athlete桌子。

另一个用于dev_test_team桌子。

CREATE TABLE dev_test_team(
   team_id int,
   team_name varchar(50),
   user_id int
);

insert into dev_test_team values (1,'indi',480);
insert into dev_test_team values (2,'usa', 472);
insert into dev_test_team values (3,'uk',  765);

CREATE TABLE dev_test_teams_athlete(
   teams_athlete_id int,
   team_id int,
   user_id int
);

insert into dev_test_teams_athlete values (12,1,380);
insert into dev_test_teams_athlete values (23,2,379);
insert into dev_test_teams_athlete values (24,3,479);
insert into dev_test_teams_athlete values (25,3,464);

CREATE TABLE dev_user(
   user_id int,
   first_name varchar(50)
);
insert into dev_user values (480,'coach1');
insert into dev_user values (472,'coach2');
insert into dev_user values (765,'coach3');
insert into dev_user values (380,'athlete1');
insert into dev_user values (379,'athlete2');
insert into dev_user values (479,'athlete3');
insert into dev_user values (464,'athlete4');

查询 1：

SELECT coach.first_name 'first_name(coach_name)',
       athlete.first_name 'first_name(athlete_name)',
       dtt.team_id,
       dtt.team_name
FROM dev_test_team dtt 
INNER JOIN dev_test_teams_athlete  dtta on dtt.team_id = dtta.team_id
LEFT JOIN dev_user coach on dtt.user_id = coach.user_id
LEFT JOIN dev_user athlete on dtta.user_id = athlete.user_id

结果：

| first_name(coach_name) | first_name(athlete_name) | team_id | team_name |
|------------------------|--------------------------|---------|-----------|
|                 coach1 |                 athlete1 |       1 |      indi |
|                 coach2 |                 athlete2 |       2 |       usa |
|                 coach3 |                 athlete3 |       3 |        uk |
|                 coach3 |                 athlete4 |       3 |        uk |

推荐阅读