我的SQL语句出了什么问题？如何正确使用JOIN？

so I'm working on a tool for fantasy baseball. I constructed this SQL statement that I thought worked initially but turned out to not do so after I added more rows.

所以我正在为幻想棒球工作。我构建了这个我认为最初工作的SQL语句但在我添加更多行之后却没有这样做。

Here is the statement:

这是声明:

SELECT
        p.player_id, position, last_name, first_name, player_type, team, s.season_gp, s.season_runs, s.season_hits, s.season_doubles, s.season_triples, s.season_home_runs, s.season_runs_batted_in, s.season_base_on_balls, s.season_strikeouts, s.season_stolen_bases, w.week_gp, w.week_runs, w.week_hits, w.week_doubles, w.week_triples, w.week_home_runs, w.week_runs_batted_in, w.week_base_on_balls, w.week_strikeouts, w.week_stolen_bases
FROM
        player AS p
LEFT JOIN   
        (SELECT
                player_id, COUNT(date) AS week_gp, SUM(runs) AS week_runs, SUM(hits) AS week_hits, SUM(doubles) AS week_doubles, SUM(triples) AS week_triples, SUM(home_runs) AS week_home_runs, SUM(runs_batted_in) AS week_runs_batted_in, SUM(base_on_balls) AS week_base_on_balls, SUM(strikeouts) AS week_strikeouts, SUM(stolen_bases) AS week_stolen_bases
        FROM
                game_mlb
        WHERE
                date >= CURDATE() - INTERVAL 1 WEEK) w
        ON p.player_id = w.player_id
LEFT JOIN   
        (SELECT
                player_id, COUNT(date) AS season_gp, SUM(runs) AS season_runs, SUM(hits) AS season_hits, SUM(doubles) AS season_doubles, SUM(triples) AS season_triples, SUM(home_runs) AS season_home_runs, SUM(runs_batted_in) AS season_runs_batted_in, SUM(base_on_balls) AS season_base_on_balls, SUM(strikeouts) AS season_strikeouts, SUM(stolen_bases) AS season_stolen_bases
        FROM
                game_mlb
        WHERE
                date >= YEAR(CURDATE())) s
        ON p.player_id = s.player_id
WHERE
        team_id = 1

Here are the PHP arrays for the tables being used.

以下是正在使用的表的PHP数组。

game_mlb:

$game_mlb = array(
  array('game_id' => '9','player_id' => '1','date' => '2013-03-31','at_bats' => '4','runs' => '0','hits' => '0','doubles' => '0','triples' => '0','home_runs' => '0','runs_batted_in' => '0','stolen_bases' => '0','base_on_balls' => '0','strikeouts' => '0','innings_pitched' => NULL,'wins' => NULL,'complete_games' => NULL,'shutouts' => NULL,'saves' => NULL,'earned_runs' => NULL,'strikeouts_pitched' => NULL,'holds' => NULL),
  array('game_id' => '10','player_id' => '2','date' => '2013-04-01','at_bats' => '4','runs' => '0','hits' => '0','doubles' => '0','triples' => '0','home_runs' => '0','runs_batted_in' => '0','stolen_bases' => '0','base_on_balls' => '1','strikeouts' => '1','innings_pitched' => NULL,'wins' => NULL,'complete_games' => NULL,'shutouts' => NULL,'saves' => NULL,'earned_runs' => NULL,'strikeouts_pitched' => NULL,'holds' => NULL)
);

player:

$player = array(
  array('player_id' => '1','player_league' => 'MLB','first_name' => 'Adrian','last_name' => 'Beltre','player_type' => 'MLB_BATTER','position' => '3B','team' => 'TEX','yahoo_id' => '6039','minors_type' => 'MLB_BR','minors_id' => 'beltre001adr','injury' => NULL,'team_id' => '1'),
  array('player_id' => '2','player_league' => 'MLB','first_name' => 'Albert','last_name' => 'Pujols','player_type' => 'MLB_BATTER','position' => '1B','team' => 'LAA','yahoo_id' => '6619','minors_type' => 'MLB_BR','minors_id' => 'pujols001jos','injury' => NULL,'team_id' => '1')
);

And here is the result I'm getting:

这是我得到的结果:

$player = array(
  array('player_id' => '1','position' => '3B','last_name' => 'Beltre','first_name' => 'Adrian','player_type' => 'MLB_BATTER','team' => 'TEX','season_gp' => '2','season_runs' => '0','season_hits' => '0','season_doubles' => '0','season_triples' => '0','season_home_runs' => '0','season_runs_batted_in' => '0','season_base_on_balls' => '1','season_strikeouts' => '1','season_stolen_bases' => '0','week_gp' => '2','week_runs' => '0','week_hits' => '0','week_doubles' => '0','week_triples' => '0','week_home_runs' => '0','week_runs_batted_in' => '0','week_base_on_balls' => '1','week_strikeouts' => '1','week_stolen_bases' => '0'),
  array('player_id' => '2','position' => '1B','last_name' => 'Pujols','first_name' => 'Albert','player_type' => 'MLB_BATTER','team' => 'LAA','season_gp' => NULL,'season_runs' => NULL,'season_hits' => NULL,'season_doubles' => NULL,'season_triples' => NULL,'season_home_runs' => NULL,'season_runs_batted_in' => NULL,'season_base_on_balls' => NULL,'season_strikeouts' => NULL,'season_stolen_bases' => NULL,'week_gp' => NULL,'week_runs' => NULL,'week_hits' => NULL,'week_doubles' => NULL,'week_triples' => NULL,'week_home_runs' => NULL,'week_runs_batted_in' => NULL,'week_base_on_balls' => NULL,'week_strikeouts' => NULL,'week_stolen_bases' => NULL)
);

This is incorrect. Beltre and Pujols should each have 1 game played with their respective stats, but Beltre is getting credited with everything.

这是不正确的。 Beltre和Pujols每人应该有1场比赛,各自的数据,但Beltre得到了一切。

Any help would be appreciated, I've been stuck on this for a while now.

任何帮助将不胜感激,我已经坚持了一段时间了。

1 个解决方案

#1

You need group by statements in your subquery:

您需要在子查询中使用group by语句:

SELECT p.player_id, position, last_name, first_name, player_type, team,
       s.season_gp, s.season_runs, s.season_hits, s.season_doubles, s.season_triples, s.season_home_runs, s.season_runs_batted_in, s.season_base_on_balls, s.season_strikeouts, s.season_stolen_bases, w.week_gp, w.week_runs, w.week_hits, w.week_doubles, w.week_triples, w.week_home_runs, w.week_runs_batted_in, w.week_base_on_balls, w.week_strikeouts, w.week_stolen_bases
FROM player p LEFT JOIN   
     (SELECT player_id, COUNT(date) AS week_gp, SUM(runs) AS week_runs, SUM(hits) AS week_hits, SUM(doubles) AS week_doubles, SUM(triples) AS week_triples, SUM(home_runs) AS week_home_runs, SUM(runs_batted_in) AS week_runs_batted_in, SUM(base_on_balls) AS week_base_on_balls, SUM(strikeouts) AS week_strikeouts, SUM(stolen_bases) AS week_stolen_bases
      FROM game_mlb
      WHERE date >= CURDATE() - INTERVAL 1 WEEK
      group by player_id
     ) w
     ON p.player_id = w.player_id LEFT JOIN   
     (SELECT player_id, COUNT(date) AS season_gp, SUM(runs) AS season_runs, SUM(hits) AS season_hits, SUM(doubles) AS season_doubles, SUM(triples) AS season_triples, SUM(home_runs) AS season_home_runs, SUM(runs_batted_in) AS season_runs_batted_in, SUM(base_on_balls) AS season_base_on_balls, SUM(strikeouts) AS season_strikeouts, SUM(stolen_bases) AS season_stolen_bases
      FROM game_mlb
      WHERE date >= YEAR(CURDATE())
      group by player_id
     ) s
     ON p.player_id = s.player_id
WHERE team_id = 1;

In most versions of SQL, your code would have returned an error. However, MySQL allows columns in the select list that are not in the group by -- and this has caused your problem.

在大多数SQL版本中,您的代码都会返回错误。但是,MySQL允许选择列表中不在组中的列 - 这导致了您的问题。

#1