56,677
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE RecordData(ID INTEGER PRIMARY KEY, Disk INTEGER, Position INTEGER, Ch INTEGER, Type INTEGER, TypeIndex INTEGER, StartTime INTEGER, EndTime INTEGER);
ID Disk Position Ch Type TypeIndex StartTime EndTime
1 1 1 1 -1 -1 100 200
2 -1 -1 1 1 -1 100 -1
3 1 2 1 -1 -1 201 -1
4 -1 -1 1 2 -1 220 260
select File.Disk, File.Position, File.StartTime, File.EndTime, Record.Ch, Record.Type, Record.StartTime, Record.EndTime
from
(select * from RecordData where Ch = 20 and StartTime < 1409961540 and (EndTime > 1409875200 or EndTime == -1) and Type == -1) as File,
(select * from RecordData where Ch = 20 and StartTime < 1409961540 and (EndTime > 1409875200 or EndTime == -1) and Type != -1) as Record
where (File.EndTime > Record.StartTime or File.EndTime == -1) and (File.StartTime < Record.EndTime or Record.EndTime == -1) limit 50;