SELECT A.id, A.date_time FROM reader A LEFT JOIN reader B ON A.id = B.id AND A.date_time > B.date_time AND A.date_time < B.date_time +15/(24*60) WHERE B.id IS NULL另外,如果id和date_time取空值或重复值的情况不多, 还可以在id,date_time上分别作索引
select id,date_time from reader a where not exists ( select 1 from reader b where a.id=b.id and a.date_time>b.date_time and a.date_time < b.date_time +15/(24*60)) and to_char(date_time,'yyyy-mm-dd')='2004-04-01' ;
not exists 替换为minus重写sql即可
1.创建表 create table ls as select * from 原表 where 1=2; 2.删除原表 truncate table 原表; 3.给表改名 rename ls to 原表;
A.id,
A.date_time
FROM reader A LEFT JOIN reader B ON
A.id = B.id
AND A.date_time > B.date_time
AND A.date_time < B.date_time +15/(24*60)
WHERE B.id IS NULL另外,如果id和date_time取空值或重复值的情况不多,
还可以在id,date_time上分别作索引
reader a where not exists (
select 1 from reader b where a.id=b.id
and a.date_time>b.date_time
and a.date_time < b.date_time +15/(24*60)) and to_char(date_time,'yyyy-mm-dd')='2004-04-01' ;
create table ls as select * from 原表 where 1=2;
2.删除原表
truncate table 原表;
3.给表改名
rename ls to 原表;