区间再补前后各一条记录
【问题】
Table rangeData：
sensorNumber rangeStartTime rangeEndTime sensorLow sensorAverage sensorHigh
1 -100 0 32 29 90
3 23 30400 902 1021 1200
3 1000 122200 3111 5400 7000
5 4222 310400 563 764 890
3 8792 10400 802 930 2019
3 0 304000 1001 1200 2190
3 1990 550400 4 31 90
4 123 20400 765 870 930
3 -56 440400 333 521 900
3 234 210400 15 98 130
3 9021 70400 100001 130000 150000
6 2111 100400 62 67 75
4 537 70400 77 80 90
3 32 87 910 1000 1102
Hello I have the following MySQL query:
SELECT sensorNumber, rangeStartTime, rangeEndTime, sensorLow, sensorAverage, sensorHigh ``FROM rangeData ``WHERE rangeStartTime >= 0 ``AND rangeEndTime <= 43200 ``AND (sensorNumber = 3)``ORDER BY sensorNumber;
How can I make it such that I can grab just 1 previous and next values outside the rangeStartTime and rangeEndTime time span?
So in english this would be said as:“Select all values within 5:00 pm and 7:00 pm, also select next value right before 5:00 pm and next value right after 7:00 pm.”
I tried the following by looking at other examples but it did not work:
SELECT sensorNumber, rangeStartTime, rangeEndTime, sensorLow, sensorAverage, sensorHigh ``FROM rangeData ``WHERE rangeStartTime >= 0 ``AND rangeEndTime <= 43200 ``AND rangeStartTime < 0 LIMIT 1``AND rangeEndTime > 43200 LIMIT 1``AND (sensorNumber = 3)``ORDER BY sensorNumber;
Each query adds 5-10 seconds of wait time so I would like to make this into one query.
别人正确答案：
(SELECT sensorNumber, rangeStartTime, rangeEndTime, sensorLow, sensorAverage, sensorHigh
FROM rangeData
WHERE (rangeStartTime BETWEEN 0 AND 43200) AND (sensorNumber = 3))
UNION
(SELECT sensorNumber, rangeStartTime, rangeEndTime, sensorLow, sensorAverage, sensorHigh
FROM rangeData
WHERE (rangeStartTime < 0) AND (sensorNumber = 3)
ORDER BY rangeStartTime DESC
LIMIT 1)
UNION
(SELECT sensorNumber, rangeStartTime, rangeEndTime, sensorLow, sensorAverage, sensorHigh
FROM rangeData
WHERE (rangeStartTime > 43200) AND (sensorNumber = 3)
ORDER BY rangeStartTime ASC
LIMIT 1)
ORDER BY sensorNumber;
【回答】
排序后选出结果集“all values within 5:00 pm and 7:00 pm”的记录序号，再取得这些记录前一条的序号和后一条的序号，再用这些序号取数据即可。
MYSQL 本身不带序号，实现有序运算代码不好搞，这里使用 SPL 实现更简单：
A |
|
1 |
$select sensorNumber, rangeStartTime, rangeEndTime, sensorLow, sensorAverage, sensorHigh from rangeData where sensorNumber=3 order by rangeStartTime |
2 |
=A1.pselect@a(rangeStartTime >= 0 && rangeEndTime <= 43200) |
3 |
=A1.m@0(A2&(A2(1)-1)&(A2.m(-1)+1)) |
A3：通过序号位置获取 A1 中指定位置的记录
