mysql - 在子查询中引用外部查询

Question

我有以下查询，它通常有效，并且应该返回涵盖定义时间范围的所有行(如果没有绝对匹配，则取最近的前一行和下一行 - 在 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`

本来希望通过引用外层select来减少两个表的subselect，但显然不喜欢

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`

有没有办法不用三表选择的查询？

Answer 1

你可以通过连接来做这样的事情:

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;

我不是 MySQL 专家，所以如果需要一些语法调整，我们深表歉意。

更新:OP 还发现了 this very nice solution .