select * from table1 where 日期<=sysdate-2 and 体温=0 ; 就可以列出所有符合条件的数据
select 学生姓名 from (select count(*) over (PARTITION by 学生姓名,DIFFDATE) diffcount,学生姓名 from ( SELECT t.* ,日期-ROW_NUMBER() OVER(PARTITION by 学生姓名 ORDER BY 日期) DIFFDATE FROM t WHERE 体温 = 0 ) )where diffcount = 3
哥们,解决了,给分吧 SQL> select * from csdn_log;S_DATE NAME WD ----------- -------------------- ---------- 2000-1-1 A 0 2000-1-1 B 0 2000-1-1 C 0 2000-1-2 A 0 2000-1-2 B 0 2000-1-2 C 36 2000-1-3 A 0 2000-1-3 B 0 2000-1-3 C 36 2000-1-4 A 0 2000-1-4 C 0 2000-1-5 C 012 rows selectedSQL> select a.name from csdn_log a,csdn_log b,csdn_log c where a.wd=0 and b.wd=0 and c.wd=0 and a.s_date=b.s_date-1 and a.s_date=c.s_date+1 and a.name=b.name and a.name=c.name group by a.name;NAME -------------------- A B
是不是连续3天以上都算连续3天的,这样的话稍微改改 select 学生姓名 from (select count(*) over (PARTITION by 学生姓名,DIFFDATE) diffcount,学生姓名 from ( SELECT t.* ,日期-ROW_NUMBER() OVER(PARTITION by 学生姓名 ORDER BY 日期) DIFFDATE FROM t WHERE 体温 = 0 ) )where diffcount >= 3
select * from table1 where 日期<=sysdate-2 and 体温=0 ;
就可以列出所有符合条件的数据
(select count(*) over (PARTITION by 学生姓名,DIFFDATE) diffcount,学生姓名 from
(
SELECT t.* ,日期-ROW_NUMBER() OVER(PARTITION by 学生姓名 ORDER BY 日期) DIFFDATE
FROM t WHERE 体温 = 0
) )where diffcount = 3
SQL> select * from csdn_log;S_DATE NAME WD
----------- -------------------- ----------
2000-1-1 A 0
2000-1-1 B 0
2000-1-1 C 0
2000-1-2 A 0
2000-1-2 B 0
2000-1-2 C 36
2000-1-3 A 0
2000-1-3 B 0
2000-1-3 C 36
2000-1-4 A 0
2000-1-4 C 0
2000-1-5 C 012 rows selectedSQL> select a.name from csdn_log a,csdn_log b,csdn_log c where a.wd=0 and b.wd=0 and c.wd=0 and a.s_date=b.s_date-1 and a.s_date=c.s_date+1 and a.name=b.name and a.name=c.name group by a.name;NAME
--------------------
A
B
select 学生姓名 from
(select count(*) over (PARTITION by 学生姓名,DIFFDATE) diffcount,学生姓名 from
(
SELECT t.* ,日期-ROW_NUMBER() OVER(PARTITION by 学生姓名 ORDER BY 日期) DIFFDATE
FROM t WHERE 体温 = 0
) )where diffcount >= 3