LIMIT 1的替代方案是提取日期范围加上之前和之后的第一个条目？

Basically, I want to select within a range + 1 entry after and 1 entry before.

基本上,我想在之前和之前的1个条目中选择+ 1个条目。

I tried like this:

我试过这样的:

SELECT DateTime, value FROM ReadingGlucoseBlood WHERE DateTime BETWEEN '2011-01-21 00:00:00' AND datetime('2011-01-21 00:00:00', '+24 hours') 
UNION 
SELECT DateTime, value FROM ReadingGlucoseBlood WHERE DateTime < '2011-01-21 00:00:00' LIMIT 1
UNION
SELECT DateTime, value FROM ReadingGlucoseBlood WHERE DateTime > datetime('2011-01-21 00:00:00', '+24 hours') LIMIT 1
ORDER BY DateTime ASC

But it turns out I can only have one LIMIT at the very end of a compound SELECT statement.. so how do I achieve this effect? (using sqlite btw)

但事实证明,我只能在复合SELECT语句的最后有一个LIMIT ..那么我该如何实现这种效果呢? (使用sqlite btw)

1 个解决方案

#1

You need to subquery the LIMITs to limit their scope

您需要子查询LIMIT以限制其范围

SELECT DateTime, value FROM ReadingGlucoseBlood WHERE DateTime BETWEEN '2011-01-21 00:00:00' AND datetime('2011-01-21 00:00:00', '+24 hours') 
UNION ALL
SELECT * FROM (
SELECT DateTime, value FROM ReadingGlucoseBlood WHERE DateTime < '2011-01-21 00:00:00'
ORDER BY DateTime DESC LIMIT 1
) X
UNION ALL
SELECT * FROM (
SELECT DateTime, value FROM ReadingGlucoseBlood WHERE DateTime > datetime('2011-01-21 00:00:00', '+24 hours')
ORDER BY DateTime ASC LIMIT 1
) Y
ORDER BY DateTime ASC