函数 row_number()
row_number() over (partition by patient_id /*需要分组的列*/ order by zyid /*排序的列*/)
sql
SELECT ZYID, patient_id ,row_number() over (partition by patient_id order by zyid) visit_id from jk_patient_in_hospital
oracle
create or replace view adminp_view_info as
select "ZYID","PATIENT_ID","MR_NO","CASE_NO","NAME","VISIT_ID","IN_DATE" from (
select a.ipd_no||+'_'||row_number() over (partition by a.ipd_no order by a.case_no ) as zyid ,a.ipd_no as patient_id ,a.mr_no ,a.case_no ,b.pat_name as name ,row_number() over (partition by a.ipd_no order by a.case_no ) as visit_id ,a.in_date from oral.adm_inp a ,
oral.SYS_PATINFO b where a.mr_no=b.mr_no )a
where a.in_date>to_date('2013-01-01','yyyy-mm-dd');
保持与源表一致的序号 需要注意条件的设置
where a.in_date>to_date('2013-01-01','yyyy-mm-dd')
