病人id 就诊时间time A 20111101 A 20111102 A 20111103 A 20111106 A 20111110 A 20111114 A 20111116 B 20111004 B 20111015 B 20111016 B 20111017 B 20111018 B 20111108 B 20111113 B 20111117 C 20110925 C 20110926 C 20110927 C 20110928 C 20110929 C 20111102 C 20111103 现在想实现这样的效果,如果一个病人连续三天有就诊记录,则把它统计出来,
病人姓名 连续三天有就诊记录的次数 A 1 B 1 C 1
select t1.* from T t1,T t2,T t3 where t1.id=t2.id and t1.id=t3.id and Trunc(t1.time)+1=Trunc(t2.time) and Trunc(t1.time)+2=Trunc(t3.time); 试试
select name, count(name) from (select b.name, min(b.time) Start_HM, max(b.time) End_HM from (select a.*, TO_NUMBER(a.time - rownum) cc from (select * from tab1 order by name, time) a) b group by b.name, b.cc) where TO_NUMBER(End_HM - Start_HM) >= 2 group by name说明:name是病人姓名,time是number类型
select name, count(name) from (select b.name, min(b.time) Start_HM, max(b.time) End_HM from (select a.*, TO_NUMBER(a.time - rownum) cc from (select * from tab1 order by name, time) a) b group by b.name, b.cc) where TO_NUMBER(End_HM - Start_HM) >= 2 group by name
提示无效数字 ,我的表时间是date类型的
select name,count(name) from (select b.name, min( to_number(to_char(b.time,'yyyymmdd'))) Start_HM, max( to_number(to_char(b.time,'yyyymmdd'))) End_HM from (select a.*, to_number(to_char(a.time,'yyyymmdd') - rownum) cc from (select * from tab1 order by name, time) a) b group by b.name, b.cc) where TO_NUMBER(End_HM - Start_HM) >=2 group by name 转化一下
A 20111101
A 20111102
A 20111103
A 20111106
A 20111110
A 20111114
A 20111116
B 20111004
B 20111015
B 20111016
B 20111017
B 20111018
B 20111108
B 20111113
B 20111117
C 20110925
C 20110926
C 20110927
C 20110928
C 20110929
C 20111102
C 20111103
现在想实现这样的效果,如果一个病人连续三天有就诊记录,则把它统计出来,
A 1
B 1
C 1
select t1.* from T t1,T t2,T t3
where t1.id=t2.id and t1.id=t3.id
and Trunc(t1.time)+1=Trunc(t2.time)
and Trunc(t1.time)+2=Trunc(t3.time);
试试
from (select b.name, min(b.time) Start_HM, max(b.time) End_HM
from (select a.*, TO_NUMBER(a.time - rownum) cc
from (select * from tab1 order by name, time) a) b
group by b.name, b.cc)
where TO_NUMBER(End_HM - Start_HM) >= 2 group by name说明:name是病人姓名,time是number类型
from (select b.name, min(b.time) Start_HM, max(b.time) End_HM
from (select a.*, TO_NUMBER(a.time - rownum) cc
from (select * from tab1 order by name, time) a) b
group by b.name, b.cc)
where TO_NUMBER(End_HM - Start_HM) >= 2 group by name
from (select b.name, min( to_number(to_char(b.time,'yyyymmdd'))) Start_HM, max( to_number(to_char(b.time,'yyyymmdd'))) End_HM
from (select a.*, to_number(to_char(a.time,'yyyymmdd') - rownum) cc
from (select * from tab1 order by name, time) a) b
group by b.name, b.cc)
where TO_NUMBER(End_HM - Start_HM) >=2 group by name
转化一下