这几乎似乎是一个范围问题-子查询中的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:{idimagenameparty}
candidate_pcts:{idcandidate_idpctweekly_game_id}
lineups:{iddate, user_id}
players:{idcandidate_idlineup_idorder}
weekly_game:{iddate}

最佳答案

您基本上在解决问题的正确道路上。本质上,嵌套子选择不了解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/

10-08 21:19