在子查询中引用外部查询 [英] Referencing outer query in subquery

问题描述

我有以下查询，该查询通常有效，并且应该返回涵盖定义时间范围的所有行(如果没有绝对匹配，则采用最接近的前一行和后一行-在

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;

我不是MySQL专家，因此如果需要一些语法调整，我深表歉意.

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

更新:OP还发现了这个非常好的解决方案.

这篇关于在子查询中引用外部查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！