error“where子句中的未知列”-怎么了？

ssgvzors 于 2021-06-17 发布在 Mysql

关注(0)|答案(1)|浏览(448)

我使用的是mysql server 5.7，我有一个查询：

select 
    regDate,
    userID,
    t.teamID,
    login
from
    tbl_user u
inner join 
    tbl_team t on u.userID = t.userID
where
    regDate >= DATE_ADD(CURDATE(), INTERVAL -2 MONTH)
    AND 
    (
        select sum(transactions) from (
            SELECT count(*) as transactions FROM tbl_pp where (fromTeamID = t.teamID or forTeamID = t.teamID) and transactionDate >= u.regDate
            union all
            SELECT count(*) as transactions FROM tbl_psc where (fromTeamID = t.teamID or toTeamID = t.teamID) and transactionDate >= u.regDate
            union all
            SELECT count(*) as transactions FROM tbl_mp where (fromTeamID = t.teamID or forTeamID = t.teamID) and transactionDate >= u.regDate
        ) as all
    ) > 0

我得到这个错误：
错误代码：1054。“where子句”中的未知列“t.teamid”
我肯定这只是个小问题，但我现在还拿不到。那柱子呢 teamID 表中存在 tbl_team . 有人给我个提示吗？

sql mysql correlated-subquery

来源：https://stackoverflow.com/questions/54026115/error-unknown-column-in-where-clause-what-is-wrong

1条答案

按热度按时间

2w3kk1z51#

不能将相关引用嵌套到多个查询中。你最好使用 exists 无论如何：

select u.regDate, u.userID, t.teamID, u.login
from tbl_user u inner join 
     tbl_team t
     on u.userID = t.userID
where u.regDate >= DATE_ADD(CURDATE(), INTERVAL -2 MONTH) and
      (exists (select 1
               from tbl_pp p
               where t.teamID in (p.fromTeamID, p.forTeamID) and
                     p.transactionDate >= u.regDate
              ) or
       exists (select 1
               from tbl_psc p
               where t.teamID in (p.fromTeamID, p.toTeamID) and
                     p.transactionDate >= u.regDate
              ) or
       exists (select 1
               from tbl_mp p
               where t.teamID in (p.fromTeamID, p.forTeamID) and
                     p.transactionDate >= u.regDate
              )
     )

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

我来回答

error“where子句中的未知列”-怎么了？

1条答案

相关问题

热门标签

最新问答