ホーム>source

コーチをアスリートの名前に一列に並べることができません

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

これは使用時の結果ですクロス結合  クエリ付きキーワード

私の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

あなたの答え
  • 解決した方法 # 1

    JOIN を試すことができます   dev_user で  テーブルを2回。

    dev_test_teams_athlete 用  テーブル。

    dev_test_team の別の1つ  テーブル。

    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 |
    
    

  • 前へ java - JPAクエリ:サブクエリをグループ化条件に結合する
  • 次へ javascript - フォーム入力は0を返します