表的结构:
aid atime ap
a5 15:30:11 -0.67
a5 15:48:23 -0.30
a5 15:57:39 -0.34
a5 16:06:50 0.33
a5 17:00:01 1.36
a6 15:02:35 2.76
a6 15:14:53 2.57
a6 15:51:58 2.23
a6 16:07:00 2.29
a6 16:53:52 1.84
a6 18:20:01 1.8
a8 15:07:22 1.4
a8 16:01:40 0.87
a8 16:10:11 -0.87
a8 17:54:07 1.22
a8 18:00:01 1.57
.......
现在想查询某时段内aid的ap值全部大于0的数据,aid有90多种
如上面的15:00到18:30只有a6的pv值全部大于0,想要查询的结果
a6 15:02:35 2.76
a6 15:14:53 2.57
a6 15:51:58 2.23
a6 16:07:00 2.29
a6 16:53:52 1.84
a6 18:20:01 1.8
------解决思路----------------------
select aid,atime,ap from tablename a where not exits(select distinct aid from tablename b where a.aid = b.aid and b.ap < 0)
------解决思路----------------------
SELECT * FROM tab a
WHERE stime BETWEEN '15:00' AND '18:30'
AND NOT EXISTS(
SELECT * FROM tab WHERE aid=a.aid
AND stime BETWEEN '15:00' AND '18:30'
AND ap<0)