作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我有以下表格(包括完整的架构,但是只有TIME值是必需的:
“要点”表
TSTAMP, LAT, LON, HDOP, ALT, FILE, EUID
"2015-12-15T20:29:54" "0.0" "0.0" "99.99" "NULL" "L151400.GPS" "080D55"
"2015-12-15T20:29:55" "0.0" "0.0" "99.99" "NULL" "L151400.GPS" "080D55"
"2015-12-15T20:29:56" "0.0" "0.0" "99.99" "NULL" "L151400.GPS" "080D55"
START, STOP, NCHANNELS, SAMPWIDTH, FRAMERATE, NFRAMES, COMPTYPE, COMPNAME, FILE, FILESIZE, EUID
"2015-12-15T22:37:00" "2015-12-15T22:39:53" "1.0" "2.0" "16000.0" "2777088.0" "NONE" "not compressed" "static/uploads/WAV/L151637.WAV" "5554688.0"
"2015-12-15T22:27:00" "2015-12-15T22:37:00" "1.0" "2.0" "16000.0" "9601024.0" "NONE" "not compressed" "static/uploads/WAV/L151627.WAV" "19202560.0"
"2015-12-15T22:17:00" "2015-12-15T22:27:00" "1.0" "2.0" "16000.0" "9601024.0" "NONE" "not compressed" "static/uploads/WAV/L151617.WAV" "19202560.0"
SELECT FILE FROM POINTS
WHERE (
TSTAMP
BETWEEN (SELECT START FROM AUDIO) AND
(SELECT STOP FROM AUDIO)
);
最佳答案
在POINTS表中查找包含TSTAMP的任何记录,该记录位于AUDIO表的任何START / STOP对之间。您可以使用以下查询。另外,请不要使用start作为列名,这是sqlite中的关键字。我用sta你可以改变列名。
选择STA,STOP,
(从POINTS中选择GROUP_CONCAT(POINTS.FILE),其中datetime(AUDIO.STA)和datetime(AUDIO.STOP)之间的datetime(POINTS.TSTAMP)表示为FIL
从AUDIO
从AUDIO
这将返回逗号分隔的范围内将出现的数据列表。我还使用了以下数据来检查
AUDIO
STA STOP
"2015-12-15T22:37:00" "2015-12-15T22:39:53"
"2015-12-15T22:27:00" "2015-12-15T22:37:00"
"2015-12-15T22:17:00" "2015-12-15T22:27:00"
POINTS
TSTAMP FILE
"2015-12-15T22:29:54" "L151400.GPS"
"2015-12-15T22:29:55" "L151400.GPS"
"2015-12-15T22:29:56" "L151400.GPS"
And following is the result
STA STOP FIL
"2015-12-15T22:37:00" "2015-12-15T22:39:53" "NULL"
"2015-12-15T22:27:00" "2015-12-15T22:37:00" "L151400.GPS,L151400.GPS,L151400.GPS"
"2015-12-15T22:17:00" "2015-12-15T22:27:00" "NULL"
关于sqlite - 当值来自多个表时,如何在SQLite中使用WHERE BETWEEN子句?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/34304629/
我是一名优秀的程序员,十分优秀!