mysql - すべての派生テーブルには、独自のエイリアスmysqlエラーメッセージが必要です

原文 mysql subquery left-join alias

私は次のクエリを持っていますが、エイリアスが必要な場所がわかりません私が得ているエラーを防ぐために誰かが助けることができます

//エラー

Every derived table must have its own alias


//クエリ

SELECT user_id, 
       SUM(win) as won, SUM(draw) As drawn, SUM(loss) as lost, 
       SUM(goals_for) as goals_for, SUM(goals_against) as goals_against, 
       SUM(goals_for) - SUM(goals_against) as goals_difference, 
       player, username, community_result_id, count(*) as played, 
       SUM(points) as points     
FROM 
    ( SELECT home_team_user_id as user_id, 
      CASE WHEN home_score > away_score THEN 1 ELSE 0 END as win, 
      CASE WHEN home_score = away_score THEN 1 ELSE 0 END as draw, CASE 
      WHEN home_score < away_score THEN 1 ELSE 0 END as loss,
      CASE WHEN home_score > away_score THEN 3 
  WHEN home_score = away_score THEN 1 ELSE 0 END points,
       home_score as goals_for, away_score as goals_against,
       users.name as player, users.username, 
       community_results.community_result_id FROM community_results 
     LEFT JOIN users ON community_results.home_team_user_id=users.id 
     UNION ALL SELECT away_team_user_id as user_id, 
     CASE WHEN away_score > home_score THEN 1 ELSE 0 END as win, 
     CASE WHEN away_score = home_score THEN 1 ELSE 0 END as draw, 
     CASE WHEN away_score < home_score THEN 1 ELSE 0 END as loss, 
     CASE WHEN home_score > away_score THEN 3 WHEN home_score = away_score 
     THEN 1 ELSE 0 END points, away_score as goals_for, home_score as goals_against, 
     users.name as player, users.username, 
     community_results.community_result_id FROM community_results 
     LEFT JOIN users ON community_results.away_team_user_id = users.id ) 
     WHERE user_id = 1 GROUP BY user_id ORDER By won desc LIMIT 1
答え
これを試して:

SELECT user_id, 
       SUM(win) as won, SUM(draw) As drawn, SUM(loss) as lost, 
       SUM(goals_for) as goals_for, SUM(goals_against) as goals_against, 
       SUM(goals_for) - SUM(goals_against) as goals_difference, 
       player, username, community_result_id, count(*) as played, 
       SUM(points) as points     
FROM 
    ( SELECT home_team_user_id as user_id, 
      CASE WHEN home_score > away_score THEN 1 ELSE 0 END as win, 
      CASE WHEN home_score = away_score THEN 1 ELSE 0 END as draw, CASE 
      WHEN home_score < away_score THEN 1 ELSE 0 END as loss,
      CASE WHEN home_score > away_score THEN 3 
  WHEN home_score = away_score THEN 1 ELSE 0 END points,
       home_score as goals_for, away_score as goals_against,
       users.name as player, users.username, 
       community_results.community_result_id FROM community_results 
     LEFT JOIN users ON community_results.home_team_user_id=users.id 
     UNION ALL SELECT away_team_user_id as user_id, 
     CASE WHEN away_score > home_score THEN 1 ELSE 0 END as win, 
     CASE WHEN away_score = home_score THEN 1 ELSE 0 END as draw, 
     CASE WHEN away_score < home_score THEN 1 ELSE 0 END as loss, 
     CASE WHEN home_score > away_score THEN 3 WHEN home_score = away_score 
     THEN 1 ELSE 0 END points, away_score as goals_for, home_score as goals_against, 
     users.name as player, users.username, 
     community_results.community_result_id FROM community_results 
     LEFT JOIN users ON community_results.away_team_user_id = users.id ) t
     WHERE user_id = 1 GROUP BY user_id ORDER By won desc LIMIT 1
関連記事

mysql - MySQLパフォーマンスレポートをリセットする方法

mysql - mysqlからのSqoopインポートnull値はnullを置き換えません

mysql - MySQL LIKE with rangeが機能しない

mysql - SQL ServerクエリはSQLServer内で正常に実行されますが、同じクエリはJDBIでは機能しません

php - 更新クエリがcodeigniterで機能していませんか?

mysql - 値が1つのテーブルにあり、別のテーブルにはない場合、どのように結合してデフォルトを与えるのですか?

php - PHPを使用した動的テーブル

mysql - usermetaデータを取得するためのカスタムWordpress SQLクエリの最適化

php - ローンの領収書(払い戻しまたはリバーストランザクション)のレポートを生成する

php - FPDFを使用してセル幅とテキスト幅を自動サイズ調整