mysql:年(日)加入子查询

qxgroojn 于 2021-06-19 发布在 Mysql

关注(0)|答案(2)|浏览(305)

试图创建一个表来汇总另一个表中的数据。摘要将按从日期字段中提取的年份显示。当我尝试将一个新字段包含到带有子查询的表中时，on子句中出现了一个1054（未知列）错误。我试过加入 date 得到同样的错误。

CREATE TABLE IF NOT EXISTS park_factor (
SELECT YEAR(games.date) AS year
    , games.home_team
    , sum(games.runs0) AS ht_runs
    , sum(games.runs1) as away_runs
    , round(leagues.rules_schedule_games_per_team/2,0) AS home_games
    , x.home_losses
FROM games 
    INNER JOIN leagues ON games.league_id=leagues.league_id
    INNER JOIN (
      SELECT YEAR(games.date) as year
          , games.home_team
          , COUNT(*) as home_losses
      FROM games
      WHERE games.runs1>runs0
      GROUP BY year, games.home_team
      ) AS x ON x.year=games.year AND x.home_team=games.home_team
GROUP BY year, games.home_team, home_games, home_losses );

mysql

来源：https://stackoverflow.com/questions/53403285/mysql-joining-to-subquery-on-yeardate

2条答案

按热度按时间

disbfnqx1#

根本没有 games.year 列，这是导致错误的原因。你需要 JOIN 使用

x.year = YEAR(games.date) AND x.home_team=games.home_team

赞(0）回复(0）举报 2021-06-19

cxfofazt2#

我是在@nick接电话的时候发现的。有效的代码是：

CREATE TABLE IF NOT EXISTS park_factor (
SELECT YEAR(games.date) AS `year`
    , games.home_team
    , sum(games.runs0) AS ht_runs
    , sum(games.runs1) as away_runs
    , round(leagues.rules_schedule_games_per_team/2,0) AS home_games
    , x.home_losses
FROM games 
    INNER JOIN leagues ON games.league_id=leagues.league_id
    INNER JOIN (
      SELECT YEAR(games.date) as `year`
          , games.home_team
          , COUNT(*) as home_losses
      FROM games
      WHERE games.runs1>runs0
      GROUP BY year, games.home_team
      ) AS x ON x.`year`=YEAR(games.date) AND x.home_team=games.home_team
GROUP BY YEAR(games.date), games.home_team, home_games, home_losses );

赞(0）回复(0）举报 2021-06-19

我来回答

mysql:年(日)加入子查询

2条答案

相关问题

热门标签

最新问答