解决方案 »
- 编写个存储过程
- 新建工作组后不能远程连接数据库
- 一个简单的程序块报错
- Oracle中一次update多条记录?
- Oracle 11G 数据库中 XML的使用
- (求助呀)请教高人:在程序中用ADO去连oracle数据库,但要以sysdba的身份去连接,那么我的连接字符串应该怎么写呢?
- 求语句:关于两张表在一次查询的结果中显示的问题
- 请问大家通过用户需求调研该如何确定表结构?怎样调研用户需求的表结构?
- oracle regexp_replace 字符串值替换问题???
- 数据库备份和恢复
- 求高手赐教:关于oracle 10g 的 isqlplus启动不了问题!
- shared_pool_size 修改,请教各位大侠
不知道可不可以用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;似乎这样才适正确些