select * from (SELECT 流水号, 住院号,lead(出院时间, 1, 0) over (ORDER BY 住院时间 asc) 上一次出院,这一次入院 FROM yourtable) where 这一次入院 - 上一次出院 < 7 应该就是这个思路
写错了,这样select * from (SELECT 流水号, 住院号,lead(出院时间, 1, 0) over (ORDER BY 住院时间 asc) 上一次出院,住院时间 这一次入院 FROM yourtable) where 这一次入院 - 上一次出院 < 7
create table HOSPITAL ( HOSPITAL_ID NUMBER(4), ---流水号 HOSPITAL_NO NUMBER(5), ---住院号 START_IN DATE, ---住院时间 END_IN DATE ---出院时间 ) select t.* from hospital t ,(select c.hospital_id chospital_id,d.hospital_id dhospital_id from (select a.* ,rownum as crownum from (select h.* from hospital h order by h.hospital_no ,h.start_in, h.end_in) a) c, (select b.* ,rownum as drownum from (select h2.* from hospital h2 order by h2.hospital_no ,h2.start_in, h2.end_in) b) d where c.crownum=drownum-1 and d.start_in-c.end_in<7 and c.hospital_no=d.hospital_no) e where t.hospital_id in chospital_id or t.hospital_id in dhospital_id
select a.* ,datediff(dd,a.出院时间,b.住院时间) from tableName a left join tableName b on a.住院号 = b.住院号 where abs(datediff(dd,a.出院时间,b.住院时间))<7 and a.流水号<>b.流水号
大概这样,楼主试下看。 select t.流水号,t.住院号,t.住院时间, (select c.出院时间 table c where c.住院号=t.住院号 and t.住院时间-c.出院时间 < 7 ) 出院时间, ( select t.住院时间-c.出院时间 < 7 table c where c.住院号=t.住院号 and t.住院时间-c.出院时间 < 7 ) 间隔天数 from table t
引用weiqiwei建的表,试验了一下,下面的SQL应该能实现LZ的要求。select S.HOSPITAL_NO, S.END_IN, E.START_IN, E.START_IN - S.END_IN from HOSPITAL S, HOSPITAL E where S.HOSPITAL_ID <> E.HOSPITAL_ID and S.HOSPITAL_NO = E.HOSPITAL_NO and E.START_IN - S.END_IN < 7 and E.START_IN - S.END_IN >= 0PS:datediff不是oracle的函数吧?
使用分析函数来实现。lead或这是lad都可以。下面给出一个例子: create table HOSPITAL ( HOSPITAL_ID NUMBER(4), ---流水号 HOSPITAL_NO NUMBER(5), ---住院号 START_IN DATE, ---住院时间 END_IN DATE ---出院时间 ) / insert into HOSPITAL (HOSPITAL_ID, HOSPITAL_NO, START_IN, END_IN) values (1152, 12345, to_date('12-11-2009', 'dd-mm-yyyy'), to_date('15-11-2009', 'dd-mm-yyyy')); insert into HOSPITAL (HOSPITAL_ID, HOSPITAL_NO, START_IN, END_IN) values (1732, 13552, to_date('15-11-2009', 'dd-mm-yyyy'), to_date('18-11-2009', 'dd-mm-yyyy')); insert into HOSPITAL (HOSPITAL_ID, HOSPITAL_NO, START_IN, END_IN) values (1111, 12345, to_date('02-11-2009', 'dd-mm-yyyy'), to_date('08-11-2009', 'dd-mm-yyyy')); insert into HOSPITAL (HOSPITAL_ID, HOSPITAL_NO, START_IN, END_IN) values (1112, 13552, to_date('21-11-2009', 'dd-mm-yyyy'), to_date('26-11-2009', 'dd-mm-yyyy')); insert into HOSPITAL (HOSPITAL_ID, HOSPITAL_NO, START_IN, END_IN) values (1113, 12345, to_date('24-11-2009', 'dd-mm-yyyy'), to_date('26-11-2009', 'dd-mm-yyyy')); insert into HOSPITAL (HOSPITAL_ID, HOSPITAL_NO, START_IN, END_IN) values (1114, 12345, to_date('05-12-2009', 'dd-mm-yyyy'), to_date('08-12-2009', 'dd-mm-yyyy')); commit; ---------------------使用下面的语句得到结果 SELECT * FROM (SELECT hospital_id ,hospital_no, start_in, end_in, lag(end_in) over(PARTITION BY hospital_no ORDER BY start_in) lost_end_in, start_in - lag(end_in) over(PARTITION BY hospital_no ORDER BY start_in) inter_days FROM hospital) a WHERE a.inter_days < 7;
create table HOSPITAL ( HOSPITAL_ID NUMBER(4), ---流水号 shbzh verchar2(10) ,---社会保障号 HOSPITAL_NO NUMBER(5), ---住院号 START_IN DATE, ---住院时间 END_IN DATE ---出院时间 ) SELECT * FROM (SELECT hospital_id ,hospital_no, shbzh, start_in, end_in, lag(end_in) over(PARTITION BY shbzh ORDER BY start_in) lost_end_in, start_in - lag(end_in) over(PARTITION BY shbzh ORDER BY start_in) inter_days FROM hospital) a WHERE a.inter_days < 7 and a.inter_days >1;似乎这样才适正确些
不知道可不可以用getdate() 和between来写
FROM yourtable) where 这一次入院 - 上一次出院 < 7
应该就是这个思路
FROM yourtable) where 这一次入院 - 上一次出院 < 7
(
HOSPITAL_ID NUMBER(4), ---流水号
HOSPITAL_NO NUMBER(5), ---住院号
START_IN DATE, ---住院时间
END_IN DATE ---出院时间
)
select t.* from hospital t ,(select c.hospital_id chospital_id,d.hospital_id dhospital_id
from (select a.* ,rownum as crownum from (select h.* from hospital h order by h.hospital_no ,h.start_in, h.end_in) a) c,
(select b.* ,rownum as drownum from (select h2.* from hospital h2 order by h2.hospital_no ,h2.start_in, h2.end_in) b) d
where c.crownum=drownum-1 and d.start_in-c.end_in<7 and c.hospital_no=d.hospital_no) e
where t.hospital_id in chospital_id or t.hospital_id in dhospital_id
values (1152, 12345, to_date('12-11-2009', 'dd-mm-yyyy'), to_date('15-11-2009', 'dd-mm-yyyy'));
insert into HOSPITAL (HOSPITAL_ID, HOSPITAL_NO, START_IN, END_IN)
values (1732, 13552, to_date('15-11-2009', 'dd-mm-yyyy'), to_date('18-11-2009', 'dd-mm-yyyy'));
insert into HOSPITAL (HOSPITAL_ID, HOSPITAL_NO, START_IN, END_IN)
values (1111, 12345, to_date('02-11-2009', 'dd-mm-yyyy'), to_date('08-11-2009', 'dd-mm-yyyy'));
insert into HOSPITAL (HOSPITAL_ID, HOSPITAL_NO, START_IN, END_IN)
values (1112, 13552, to_date('21-11-2009', 'dd-mm-yyyy'), to_date('26-11-2009', 'dd-mm-yyyy'));
insert into HOSPITAL (HOSPITAL_ID, HOSPITAL_NO, START_IN, END_IN)
values (1113, 12345, to_date('24-11-2009', 'dd-mm-yyyy'), to_date('26-11-2009', 'dd-mm-yyyy'));
insert into HOSPITAL (HOSPITAL_ID, HOSPITAL_NO, START_IN, END_IN)
values (1114, 12345, to_date('05-12-2009', 'dd-mm-yyyy'), to_date('08-12-2009', 'dd-mm-yyyy'));
commit;
select a.* ,datediff(dd,a.出院时间,b.住院时间)
from tableName a
left join tableName b
on a.住院号 = b.住院号
where abs(datediff(dd,a.出院时间,b.住院时间))<7
and a.流水号<>b.流水号
select t.流水号,t.住院号,t.住院时间,
(select c.出院时间 table c where c.住院号=t.住院号
and t.住院时间-c.出院时间 < 7
) 出院时间,
( select t.住院时间-c.出院时间 < 7 table c where c.住院号=t.住院号
and t.住院时间-c.出院时间 < 7
) 间隔天数
from table t
from HOSPITAL S, HOSPITAL E
where S.HOSPITAL_ID <> E.HOSPITAL_ID
and S.HOSPITAL_NO = E.HOSPITAL_NO
and E.START_IN - S.END_IN < 7
and E.START_IN - S.END_IN >= 0PS:datediff不是oracle的函数吧?
create table HOSPITAL
(
HOSPITAL_ID NUMBER(4), ---流水号
HOSPITAL_NO NUMBER(5), ---住院号
START_IN DATE, ---住院时间
END_IN DATE ---出院时间
)
/
insert into HOSPITAL (HOSPITAL_ID, HOSPITAL_NO, START_IN, END_IN)
values (1152, 12345, to_date('12-11-2009', 'dd-mm-yyyy'), to_date('15-11-2009', 'dd-mm-yyyy'));
insert into HOSPITAL (HOSPITAL_ID, HOSPITAL_NO, START_IN, END_IN)
values (1732, 13552, to_date('15-11-2009', 'dd-mm-yyyy'), to_date('18-11-2009', 'dd-mm-yyyy'));
insert into HOSPITAL (HOSPITAL_ID, HOSPITAL_NO, START_IN, END_IN)
values (1111, 12345, to_date('02-11-2009', 'dd-mm-yyyy'), to_date('08-11-2009', 'dd-mm-yyyy'));
insert into HOSPITAL (HOSPITAL_ID, HOSPITAL_NO, START_IN, END_IN)
values (1112, 13552, to_date('21-11-2009', 'dd-mm-yyyy'), to_date('26-11-2009', 'dd-mm-yyyy'));
insert into HOSPITAL (HOSPITAL_ID, HOSPITAL_NO, START_IN, END_IN)
values (1113, 12345, to_date('24-11-2009', 'dd-mm-yyyy'), to_date('26-11-2009', 'dd-mm-yyyy'));
insert into HOSPITAL (HOSPITAL_ID, HOSPITAL_NO, START_IN, END_IN)
values (1114, 12345, to_date('05-12-2009', 'dd-mm-yyyy'), to_date('08-12-2009', 'dd-mm-yyyy'));
commit;
---------------------使用下面的语句得到结果
SELECT *
FROM (SELECT hospital_id ,hospital_no,
start_in,
end_in,
lag(end_in) over(PARTITION BY hospital_no ORDER BY start_in) lost_end_in,
start_in - lag(end_in) over(PARTITION BY hospital_no ORDER BY start_in) inter_days
FROM hospital) a
WHERE a.inter_days < 7;
(
HOSPITAL_ID NUMBER(4), ---流水号
shbzh verchar2(10) ,---社会保障号
HOSPITAL_NO NUMBER(5), ---住院号
START_IN DATE, ---住院时间
END_IN DATE ---出院时间
) SELECT *
FROM (SELECT hospital_id ,hospital_no, shbzh,
start_in,
end_in,
lag(end_in) over(PARTITION BY shbzh ORDER BY start_in) lost_end_in,
start_in - lag(end_in) over(PARTITION BY shbzh ORDER BY start_in) inter_days
FROM hospital) a
WHERE a.inter_days < 7
and a.inter_days >1;似乎这样才适正确些