select x.病人科室id,z.名称,x.病人id,x.主页id,x.姓名,x.性别,x.年龄,x.住院费用,y.手术室金额,y.手术金额,y.麻醉金额 from
------住院
(select t.病人科室id,a.病人id,a.主页id,a.姓名,a.性别,a.年龄,sum(实收金额) as 住院费用
from 住院费用记录 t,病案主页 a
where t.病人id=a.病人id
and t.主页id=a.主页id
and a.出院日期 between /*B0*/TO_DATE('2023-10-19 00:00:00','YYYY-MM-DD HH24:MI:SS')/*E0*/ and /*B1*/TO_DATE('2023-10-19 23:59:59','YYYY-MM-DD HH24:MI:SS')/*E1*/
group by t.病人科室id,a.病人id,a.主页id,a.姓名,a.性别,a.年龄) x,
(select 病人科室id,病人id,主页id,姓名,性别,年龄,sum(手术室金额) as 手术室金额,sum(手术金额) as 手术金额,sum(麻醉金额) as 麻醉金额 from (
----手术室
select t.病人科室id,a.病人id,a.主页id,a.姓名,a.性别,a.年龄,0,sum(实收金额) as 手术室金额,0 as 手术金额,0 as 麻醉金额
from 住院费用记录 t,病案主页 a
where t.病人id=a.病人id and t.主页id=a.主页id
and exists (select 1 from 部门表 g,部门性质说明 h where g.id=h.部门id and t.开单部门id=g.id and h.工作性质 in ('手术','麻醉'))
and a.出院日期 between /*B0*/TO_DATE('2023-10-19 00:00:00','YYYY-MM-DD HH24:MI:SS')/*E0*/ and /*B1*/TO_DATE('2023-10-19 23:59:59','YYYY-MM-DD HH24:MI:SS')/*E1*/
group by t.病人科室id,a.病人id,a.主页id,a.姓名,a.性别,a.年龄
union all
------手术
select t.病人科室id,a.病人id,a.主页id,a.姓名,a.性别,a.年龄,0,0,sum(实收金额) as 手术金额,0
from 住院费用记录 t,病案主页 a
where t.病人id=a.病人id and t.主页id=a.主页id
and exists (select 1 from 部门表 g,部门性质说明 h where g.id=h.部门id and t.开单部门id=g.id and h.工作性质 in ('手术','麻醉'))
and t.收费类别='F'
and a.出院日期 between /*B0*/TO_DATE('2023-10-19 00:00:00','YYYY-MM-DD HH24:MI:SS')/*E0*/ and /*B1*/TO_DATE('2023-10-19 23:59:59','YYYY-MM-DD HH24:MI:SS')/*E1*/
group by t.病人科室id,a.病人id,a.主页id,a.姓名,a.性别,a.年龄
union all
----------麻醉
select t.病人科室id,a.病人id,a.主页id,a.姓名,a.性别,a.年龄,0,0,0,sum(实收金额) as 麻醉金额
from 住院费用记录 t,病案主页 a
where t.病人id=a.病人id and t.主页id=a.主页id
and exists (select 1 from 部门表 g,部门性质说明 h where g.id=h.部门id and t.开单部门id=g.id and h.工作性质 in ('手术','麻醉'))
and t.收费类别='G'
and a.出院日期 between /*B0*/TO_DATE('2023-10-19 00:00:00','YYYY-MM-DD HH24:MI:SS')/*E0*/ and /*B1*/TO_DATE('2023-10-19 23:59:59','YYYY-MM-DD HH24:MI:SS')/*E1*/
group by t.病人科室id,a.病人id,a.主页id,a.姓名,a.性别,a.年龄)
group by 病人科室id,病人id,主页id,姓名,性别,年龄) y,部门表 z
where x.病人科室id=y.病人科室id
and x.病人id=y.病人id
and x.主页id=y.主页id
and x.病人科室id=z.id
and x.病人科室id=/*B2*/223/*E2*/
order by 名称,病人id