这几乎似乎是一个范围问题-子查询中的select语句无法识别表'candidate':
SELECT
candidate.id AS id,
candidate.image AS image,
candidate.name AS name,
candidate.party AS party,
player.order AS player_order,
c_pcts.pct AS pct
FROM `candidate`
INNER JOIN players player ON player.candidate_id = candidate.id
INNER JOIN lineups lineup ON player.lineup_id = lineup.id
INNER JOIN (
SELECT
pct
FROM candidate_pcts p
INNER JOIN weekly_game game ON p.weekly_game_id = (
SELECT id FROM weekly_game ORDER BY date DESC LIMIT 1
) WHERE p.candidate_id = candidate.id
) c_pcts
WHERE lineup.id = '31'
ORDER BY player.order ASC
给出错误:“'where子句'中的未知列'candidate.id'。”如果我代替“ FROM候选人_pcts p”
FROM candidate_pcts p, candidate c
那么就看不到“ p.weekly_game_id” ...是吗?
似乎我需要以某种方式为子查询标识“候选”表,但我尝试执行的所有操作只会使我误入歧途。而且我尝试了很多事情:表格的顺序,在我想起的任何地方都明确地指出它们,反引号。我应该注意,嵌套子查询的工作原理很像。这里又是:
SELECT
pct
FROM `candidate_pcts`
INNER JOIN weekly_game game ON candidate_pcts.weekly_game_id = (
SELECT id FROM weekly_game ORDER BY date DESC LIMIT 1
) WHERE candidate_pcts.candidate_id = '5'
当然,在那里有一个硬编码的id值。如果需要,我可以提供数据库结构,但这已经很久了。 “ weekly_game”表只是每个候选人每个星期的一组分数,我们只需要最近一周的分数,因此是“ ORDER BY date DESC LIMIT 1”子句。
非常感谢你花时间陪伴。
表格:
表
candidate
:{id
,image
,name
,party
}表
candidate_pcts
:{id
,candidate_id
,pct
,weekly_game_id
}表
lineups
:{id
,date, user_id
}表
players
:{id
,candidate_id
,lineup_id
,order
}表
weekly_game
:{id
,date
} 最佳答案
您基本上在解决问题的正确道路上。本质上,嵌套子选择不了解candidate.id
。分解查询后,只需查看有问题的子选择即可:
SELECT
pct
FROM candidate_pcts p
INNER JOIN weekly_game game ON p.weekly_game_id = (
SELECT id FROM weekly_game ORDER BY date DESC LIMIT 1
) WHERE p.candidate_id = candidate.id
您可以看到,除了where子句外,该查询中没有对
candidate
表的任何引用,因此这是未知列。因为从本质上讲,子选择是在引用它的外部选择之前进行的,所以子选择必须是独立的可执行查询。
关于mysql - mysqli子查询未知列,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/31082226/