我有一个表svn1:
id | date | startdate
23 2002-12-04 2000-11-11
23 2004-08-19 2005-09-10
23 2002-09-09 2004-08-23
从svn1中选择id,startdate,其中startdate> =(从svn1中选择max(date),其中id = svn1.id);
现在问题是如何让子查询知道id与外部查询中的id匹配.显然id = svn1.id不会工作.谢谢!
If you have the time to read more:
这真的是一个简化版本,询问我在这里想要做什么.我的实际查询是这样的
select
id, count(distinct archdetails.compname)
from
svn1,svn3,archdetails
where
svn1.name='ant'
and svn3.name='ant'
and archdetails.name='ant'
and type='Bug'
and svn1.revno=svn3.revno
and svn3.compname=archdetails.compname
and
(
(startdate>=sdate and startdate<=edate)
or
(
sdate<=(select max(date) from svn1 where type='Bug' and id=svn1.id)
and
edate>=(select max(date) from svn1 where type='Bug' and id=svn1.id)
)
or
(
sdate>=startdate
and
edate<=(select max(date) from svn1 where type='Bug' and id=svn1.id)
)
)
group by id LIMIT 0,40;
当您注意到从svn1中选择max(date)时,type =’Bug’和id = svn1.id必须多次计算.
我可以只计算一次并使用AS存储它,然后再使用该变量.主要问题是纠正id = svn1.id,以便正确地将它等同于外表中的id.