边界范围内的SQL查询记录和范围外的max/min [英] SQL query records within a range of boundaries and max/min outside the range

问题描述

我有以下三个简单的 T-SQL 查询.第一个是获取某个边界范围内的记录(DATETIME 类型):

I have the following three simple T-SQL queries. First one is to get records within a range of boundaries (DATETIME type):

 SELECT value, timestamp 
 FROM myTable
 WHERE timestamp BETWEEN @startDT AND @endDT

第二个是获取最接近@startDT的记录(DATETIME类型)

the second one is to get the closest record to @startDT (DATETIME type)

 SELECT TOP 1
   value, timestamp
 FROM myTable
 WHERE timestamp > @startDT
 ORDER BY timestamp DESC

最后一个是在@endDT之后获取最近的记录:

and the last one is to get the closest record after @endDT:

 SELECT TOP 1
   value, timestamp
 FROM myTable
 WHERE timestamp < @endDT
 ORDER BY timestamp ASC

我想把以上三个查询的所有记录作为一组记录.我尝试使用 UNION，但似乎 UNION 中的子查询不允许 ORDER BY 子句.有没有有效的方法来获得我的结果?

I would like to get all the records of above three queries as one group of records. I tried to use UNION, but it seems that sub-queries within UNION does not allow ORDER BY clause. Is there efficient way to get my result?

 . .  * | * * * * * | * . . .
      start        end

上图简单地把*s的记录作为我需要的记录，而|...|是边界.

The above graph simply shows the records of *s as my required records, and |...| is the boundaries.

顺便说一下，myTable 中的数据量是巨大的.我的理解 UNION 不是从 UNION 获取数据的有效方法.在没有 UNION 的情况下获取数据的有效方法是什么?

By the way, the amount of data in myTable is huge. My understanding UNION is not an efficient way to get data from UNIONs. Any efficient way to get data without UNION?

推荐答案

如你所愿，没有 UNION.

As you wish, without UNION.

MySQL(已测试)

MySQL (TESTED)

SELECT 
     dv1.timestamp, dv1.values
FROM 
     myTable AS dv1
WHERE 
    dv1.timestamp 
    BETWEEN (
           SELECT dv2.timestamp 
           FROM myTable AS dv2
           WHERE dv2.timestamp < '@START_DATE' 
           ORDER BY dv2.timestamp DESC 
           LIMIT 1
           )
    AND ( SELECT dv3.timestamp 
          FROM myTable AS dv3
          WHERE dv3.timestamp > '@END_DATE' 
          ORDER BY dv3.timestamp ASC 
          LIMIT 1
        )

编辑抱歉，我忘了注意 T-SQL.

EDIT Sorry, I forgot to notice about T-SQL.

T-SQL(未测试)

SELECT 
     dv1.timestamp, dv1.values
FROM 
     myTable AS dv1
WHERE 
    dv1.timestamp 
    BETWEEN (
           SELECT TOP 1 dv2.timestamp 
           FROM myTable AS dv2
           WHERE dv2.timestamp >  @START_DATE 
           ORDER BY dv2.timestamp DESC
           )
    AND ( SELECT TOP 1 dv3.timestamp 
          FROM myTable AS dv3
          WHERE dv3.timestamp <  @END_DATE 
          ORDER BY dv3.timestamp ASC
        )

注意 如果结果不正确，您可以交换子查询(即运算符和 ASC/DESC).

Note If the result is not right, you could just exchange the sub queries (i.e. operators, and ASC/DESC).

开箱即用:)

这篇关于边界范围内的SQL查询记录和范围外的max/min的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！