tj.sql

这个是SQL2000下

select a.inpatient_no 住院号BAZA01,a.name 病人姓名BAZA02,c.name 性别BAZA03,a.birth_date 出生日期BAZA04,
datediff(yy,a.birth_date,a.admiss_date)年龄baza06,h.marry_code 婚状baza07,h.occupation_code 职业baza08,h.birth_place 出生地baza09,
h.nation_code 民族baza10,h.country 国籍bazaa2,h.employer_tel 单位电话baza11,h.employer_name 单位名称bazaa3,h.employer_zipcode 单位邮编bazaa4,
h.birth_place 省市县地址baza12,h.home_street 街村地址baza13,h.home_zipcode 邮编baza14,h.relation_name 关系人姓名baza15,
h.relation_code 关系baza16,h.relation_district 关系地址县baza17,h.relation_street 关系村baza18,h.relation_tel 关系电话baza19,
a.admiss_status 入院情况baza20,b.name 入院科别baza21,b.abbname 出院科别baza23,a.admiss_date 入院日期baza24,f.dis_type 出院方式baza26,
a.dis_date 出院日期baza27,datediff(dd,a.admiss_date,a.dis_date) 住院天数baza29,a.diagnosis_date 确诊日期baza30,
datediff(dd,a.admiss_date,a.diagnosis_date) 确诊日期baza31,a.dept_director 科主任bazab1,g.name 主任医师baza32,a.consult_physician 主治医师baza33,
a.refer_physician 住院医师baza34,f.mz_diag_doctor 门诊医师baza35,f.learn_doctor 进修医师bazab2,f.practice_doctor 实习医师bazab3,
f.encoder 病案整理者bazabd,a.clinic_diag 门诊诊断baza36,a.clinic_diag 入院诊断baza37,d.dis_diag_type 出院主诊断baza39,
d.dis_diag_status 转归baza41,f.diag_conform3 门出符合baza43,f.diag_conform4 入出符合baza44,f.diag_conform6 临床病理符合bazac1,
f.diag_conform7 放射病理符合bazac2,a.blood_type 血型baza45,f.salvage_times 抢救次数baza46,f.salvage_success 成功次数baza47,
f.cure_type 治疗类别baza55,d.dis_diag_pathology 病理标志baza61,f.salvage_method 抢救方法baza63,a.clinic_diag 入院主诊断ICD10baza64,
a.admiss_diag 出院主诊断ICD10baza65,a.charge_type 医疗付款方式baza82,e.charge_fee 西药费baza86,
a.admiss_diag_str 疾病名称
from view_zy_allpatient a,zd_unit_code b,zd_sex_code c,ba_first_page1 d,
ba_first_page6 e,ba_first_page4 f,a_employee_mi g,a_patient_mi h
where a.dis_date>='2005-09-20'
and a.dis_date<='2006-09-30'
and a.sex=c.code
and a.dept=b.code
and g.code like f.chief_doctor
and a.inpatient_no=d.inpatient_no
and a.inpatient_no=e.inpatient_no
and a.inpatient_no=f.inpatient_no
and a.inpatient_no=h.inpatient_no
and e.charge_item ='001'
and d.dis_diag_no='1'
group by a.bed_no,a.inpatient_no,a.name,c.name,a.birth_date,h.marry_code,h.occupation_code,h.birth_place,h.nation_code,
h.country,h.employer_tel,h.employer_name,h.employer_zipcode,h.birth_place,h.home_street,e.charge_fee,
h.home_zipcode,h.relation_name,h.relation_code,h.relation_district,h.relation_street,
h.relation_tel,a.admiss_status,b.name,b.abbname,a.admiss_date,f.dis_type,
a.dis_date,datediff(dd,a.admiss_date,a.dis_date),a.diagnosis_date,datediff(dd,a.diagnosis_date,a.admiss_date ),
a.dept_director,g.name,a.consult_physician,a.refer_physician,f.mz_diag_doctor,
f.learn_doctor,f.practice_doctor,f.encoder,a.clinic_diag,d.dis_diag_type,d.dis_diag_status,
f.diag_conform3,f.diag_conform4,f.diag_conform6,f.diag_conform7,a.blood_type,
f.salvage_times,f.salvage_success,f.cure_type,d.dis_diag_pathology,f.salvage_method,
a.clinic_diag,a.admiss_diag,a.charge_type,a.admiss_diag_str
order by a.dis_date


--select * from a_patient_mi
--select * from zd_unit_code
--select * from ba_field_control **
--select * from view_zy_allpatient where patient_id='000000002700'
--select * from view_zy_allpatient where inpatient_no='138203'
--select * from view_zy_allpatient where name='郭波'
--select * from view_zy_allpatient
--select * from zy_actpatient
--select * from ba_first_page6
--select * from zd_sex_code
--select * from a_employee_mi
--select * from gh_detail_charge
--select * from zy_deposit_file where depo_amount='1000' 病人交款记录
--select * from zy_patient_total_charge  病人费用表
--select * from zy_actpatient
--select * from zy_receipt_charge
--select sum(charge_amount*charge_price) from view_zy_detail_charge_b where patient_id='000000002700' 求X人的总费用