我正在尝试在mysql中构建一个如下所示的查询:
select
t1.id
,t1Next.code as nextCode
,t1Prev.code as prevCode
from
t1
,(select * from t2 where idMain in (idList)) as t2Main
,(select * from t2 where idAssoc in (idList)) as t2Assoc
inner join t1 as t1Next on t1Next.id = t2Main.idAssoc
inner join t1 as t1Prev on t1Prev.id = t2Assoc.idMain
where t1.id in (idList)
and t2Main.idMain = t1.id
and t2Assoc.idAssoc = t1.id
它会带着错误回来 Unknown column 't2Main.idAssoc' in 'on clause'
.
此查询适用于:
select
t1.id
,t1Next.code as nextCode
from
t1
,(select * from t2 where idMain in (idList)) as t2Main
inner join t1 as t1Next on t1Next.id = t2Main.idAssoc
where t1.id in (idList)
and t2Main.idMain = t1.id
像这样添加第二个子查询会中断查询并导致上述错误:
select
t1.id
,t1Next.code as nextCode
from
t1
,(select * from t2 where idMain in (idList)) as t2Main
,(select * from t2 where idAssoc in (idList)) as t2Assoc
inner join t1 as t1Next on t1Next.id = t2Main.idAssoc
where t1.id in (idList)
and t2Main.idMain = t1.id
为什么会这样?
暂无答案!
目前还没有任何答案,快来回答吧!