SQL查询记录在范围内，最大/最小在范围之外

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

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

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

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

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

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

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

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

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

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?

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

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

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

上面的图表仅仅显示了我所需要的*s记录，以及|…|是边界。

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?

顺便说一下，myTable中的数据量很大。我所理解的UNION不是从UNION获取数据的有效方法。有没有什么有效的方法来获得数据而没有结合?

5 个解决方案

#1

As you wish, without UNION.

如你所愿，没有联合。

MySQL (TESTED)

MySQL(测试)

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
        )

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

编辑对不起，我忘记注意T-SQL了。

T-SQL (NOT TESTED)

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
        )

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

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

Think out of the box :)

跳出框框:)

#2

U can use max/min to get value u need. Order by +top 1 isnt best way to get max value, what i can see in ur querys. To sort n items its O(n to power 2), getting max should be only O(n)

你可以用最大值/最小值来得到你需要的值。按+ 1排序并不是获得最大值的最好方法，我在你的问题中可以看到。要对n项进行排序它的O(n的2次方)，取最大值应该只有O(n)

#3

SELECT value, timestamp 
 FROM myTable
 WHERE timestamp BETWEEN @startDT AND @endDT
union
select A.Value, A.TimeStamp
From (
 SELECT TOP 1
   value, timestamp
 FROM myTable
 WHERE timestamp > @startDT
 ORDER BY value, timestamp DESC ) A
Union
Select A.Value, A.TimeStamp
From (
 SELECT TOP 1
   value, timestamp
 FROM myTable
 WHERE timestamp < @endDT
 ORDER BY value, timestamp ASC ) A

#4

The second and third queries in your post don't make much sense because

你文章中的第二个和第三个问题没有多大意义，因为

WHERE timestamp > @startDT

and

和

WHERE timestamp < @endDT

result in timestamps INSIDE the range, but your descriptions

结果在范围内产生时间戳，但你的描述

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

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

上面的图表仅仅显示了我所需要的*s记录，以及|…|是边界。

means something different.

意味着不同的东西。

So following the descriptions and using the following mapping

因此遵循描述并使用以下映射

myTable = Posts
value = score
timestamp  = creationdate 

I wrote this query on data.stackexchange.com (modified from exodream's answer but with the comparison operators in the correct reverse direction)

我在data.stackexchange.com(从exodream的答案修改而来，但比较操作符的方向正好相反)上写了这个查询。

DECLARE @START_DATE datetime
DECLARE @END_DATE datetime
SET @START_DATE = '2010-10-20'
SET @END_DATE = '2010-11-01'

SELECT score, 
       creationdate 
FROM   posts 
WHERE  creationdate BETWEEN (SELECT TOP 1 creationdate 
                             FROM   posts 
                             WHERE  creationdate < @START_DATE 
                             ORDER  BY creationdate DESC) 
                             AND 
                                   (SELECT TOP 1 creationdate 
                                    FROM   posts 
                                    WHERE  creationdate > @END_DATE 
                                    ORDER  BY creationdate ASC) 
ORDER by creationDate

Which outputs

的输出

score creationdate        
----- ------------------- 
4     2010-10-19 23:55:48 
3     2010-10-20 2:24:50   
6     2010-10-20 2:55:54  
...
...
7     2010-10-31 23:14:48 
4     2010-10-31 23:18:17 
4     2010-10-31 23:18:48 
0     2010-11-01 3:59:38  

(382 row(s) affected)

Note how the first row and last rows are just outside the limits of the range

注意第一行和最后一行是如何超出范围的

#5

You can put those ordered queries into subqueries to get around not being able to UNION them directly. A little annoying, but it'll get you what you want.

您可以将这些有序的查询放入子查询中，以避免无法直接联合它们。有点烦人，但它会让你得到你想要的。

SELECT value, timestamp 
 FROM myTable
 WHERE timestamp BETWEEN @startDT AND @endDT
 UNION
 SELECT value, timestamp 
 FROM (
 SELECT TOP 1
   value, timestamp
 FROM myTable
 WHERE timestamp > @startDT
 ORDER BY value, timestamp DESC 
 ) x
 UNION
 SELECT value, timestamp
 FROM (
 SELECT TOP 1
   value, timestamp
 FROM myTable
 WHERE timestamp < @endDT
 ORDER BY value, timestamp ASC
 ) x