mysql 子查询引用外部表,在子查询中引用外部查询

I have the following query, which generally works, and is supposed to return all rows covering the define timeframe (taking the closest prior and next rows if no absolut match - outlined at http://www.orafaq.com/node/1834)

SELECT * FROM table

WHERE id=__ID__ AND `date` BETWEEN

IFNULL((SELECT MAX(`date`) FROM table WHERE id=__ID__ AND `date`<=__LOWERLIMIT__), 0)

AND

IFNULL((SELECT MIN(`date`) FROM table WHERE id=__ID__ AND `date`>=__UPPERLIMIT__), UNIX_TIMESTAMP())

ORDER BY `date`

but was hoping to reduce the two table subselects by referencing to the outer select, but obviously it doesnt like it

SELECT * FROM (SELECT * FROM table WHERE id=__ID__) b

WHERE `date` BETWEEN

IFNULL((SELECT MAX(`date`) FROM b WHERE `date`<=__LOWERLIMIT__), 0)

AND

IFNULL((SELECT MIN(`date`) FROM b WHERE `date`>=__UPPERLIMIT__), UNIX_TIMESTAMP())

ORDER BY `date`

Is there a way to have the query without the three table selects?

解决方案

You can do something like this with a join:

select * from table a

inner join (

select id,

max(

if(`date` <= __LOWERLIMIT__ ,`date`, 0)

) as min_date,

min(

if(`date` >= __UPPERLIMIT__ , `date`, UNIX_TIMESTAMP())

) as max_date

from table

where id = __ID__

group by id

) range on

range.id = a.id and

a.`date` between min_date and max_date;

I'm not a MySQL expert, so apologies if a bit of syntax tweaking is needed.

Update: the OP also found this very nice solution.