1.查询有费用凭单的男病人的姓名,费用发生日期,开凭单的医生姓名,凭单所属的科室名称
//xm值重复,重新命名别名
select a.xm brxm,b.fyfsrq,c.xm ysxm,d.ksmc
from hr.per_natl a,
hr.patient_script_genl b
hr.doctor_ c
hr.detp d
//关联条件
where a.grbm=b.grbm
and b.ysbm = c.ysbm
and b.ksbm = c.ksbm
and a.xb='1'
2.查询有费用凭单的病人姓名,用过的药品名称,药品单价
//distinct去重
select distinct a.xm brxm,d.ylxmmc ypmc,d.dj ypdj
from hr.per_natl a,
hr.petaient_script_genl b,
hr.patient_script_detl c,
hr.medi_item d
//关联条件
where a.grbm = b.grbm
and b.fyid=c.fyid
and c.ylxmbm = d.ylxmbm
and d.ylxmlb='1'
3.查询有费用凭单的病人的姓名,用过的药品的名称、单价,使用过的次数,使用的总数量
//select语句超过五个字段放下一行
//group by时不需要distinct(去重)
select a.xm brxm,d.ylxmmc ypmc,d.dj ypdj,
count(c.ylxmbm) sycs,sum(c.sl) zsl
from hr.per_natl a,
hr.petaient_script_genl b,
hr.patient_script_detl c,
hr.medi_item d
//关联条件
where a.grbm = b.grbm
and b.fyid=c.fyid
and c.ylxmbm = d.ylxmbm
and d.ylxmlb='1'
//分组不需要别名
group by d.ylxmbm,a.xm,d.ylxmmc,d.dj;
4.查询开过凭单的医生的姓名,所属科室名称
select a.xm,b,ksmc
from hr.doctor a,hr.dept b
where a.ksbm = b.ksbm
//判定存在费用记录 select 常量,只是用作组成结构
and exists(select '1' hr.patient_script_genl g
where g.ysbm = a.ysbm);