CREATE VIEW v_patient_waitlist AS
select tp.patientid,tpr.serialno,tpr.registid,tpr.regist_typeid,tpr.validdate1,tpr.validdate2,tr.empid,tp.patient_name,tp.patient_telephone,tp.patient_sexId,tp.birth_date,tpr.date_typeid,tpr.date_typename, NULL as book_status,'登记' as status_name
from t_pa_register tpr
join t_patient tp
on tpr.patientid=tp.patientid
LEFT JOIN t_register tr
ON tr.registid=tpr.registid
where tpr.admission_statue='00'
union
select tp.patientid,tpb.bookid,tpb.registid,NULL as regist_typeid,NULL as validdate1,NULL as validdate2,NULL as empid,tp.patient_name,tp.patient_telephone,tp.patient_sexId,tp.birth_date,tpb.date_typeid,tpb.date_typename, tpb.book_status,'预约' as status_name
from t_patient_book tpb
join t_patient tp
on tpb.patientid = tp.patientid
where tpb.book_status='00'
select tp.patientid,tpr.serialno,tpr.registid,tpr.regist_typeid,tpr.validdate1,tpr.validdate2,tr.empid,tp.patient_name,tp.patient_telephone,tp.patient_sexId,tp.birth_date,tpr.date_typeid,tpr.date_typename, NULL as book_status,'登记' as status_name
from t_pa_register tpr
join t_patient tp
on tpr.patientid=tp.patientid
LEFT JOIN t_register tr
ON tr.registid=tpr.registid
where tpr.admission_statue='00'
union
select tp.patientid,tpb.bookid,tpb.registid,NULL as regist_typeid,NULL as validdate1,NULL as validdate2,NULL as empid,tp.patient_name,tp.patient_telephone,tp.patient_sexId,tp.birth_date,tpb.date_typeid,tpb.date_typename, tpb.book_status,'预约' as status_name
from t_patient_book tpb
join t_patient tp
on tpb.patientid = tp.patientid
where tpb.book_status='00'