| 说是除数为0了 应该要写case when 语句 可是我不知道怎么加进去……感觉头都晕了 select dept.SORT_ORDER, dept.id, dept.department_chinese_name, sum(a.rs) rs, sum(a.totaldays) totaldays, sum(a.avagdays) avagdays, sum(a.qty) qty, sum(a.dddvalue) dddvalue, ROUND(sum(a.dddvalue) / sum(a.totaldays) * 100, 2 ) ddd强度, SUM(a.tk) tk, round(sum(a.tk) / sum(a.totaldays) * 100, 2) tk强度 from (select r.dept_id dept_id, count(r.id) rs, sum(CASE WHEN to_date(to_char(r.leave_dept_date, 'yyyy-MM-dd hh24:mi:ss'),'yyyy-MM-dd hh24:mi:ss') - to_date(to_char(r.first_insection_date, 'yyyy-MM-dd hh24:mi:ss'), 'yyyy-MM-dd hh24:mi:ss') = 0 THEN 1 else to_date(to_char(r.leave_dept_date, 'yyyy-MM-dd hh24:mi:ss'), 'yyyy-MM-dd hh24:mi:ss') - to_date(to_char(r.first_insection_date, 'yyyy-MM-dd hh24:mi:ss'), 'yyyy-MM-dd hh24:mi:ss') end) totaldays, round(sum(CASE WHEN to_date(to_char(r.leave_dept_date, 'yyyy-MM-dd hh24:mi:ss'), 'yyyy-MM-dd hh24:mi:ss') - to_date(to_char(r.first_insection_date, 'yyyy-MM-dd hh24:mi:ss'), 'yyyy-MM-dd hh24:mi:ss') = 0 THEN 1 else to_date(to_char(r.leave_dept_date, 'yyyy-MM-dd hh24:mi:ss'), 'yyyy-MM-dd hh24:mi:ss') - to_date(to_char(r.first_insection_date, 'yyyy-MM-dd hh24:mi:ss'), 'yyyy-MM-dd hh24:mi:ss') end) / count(r.id)) avagdays, 0 qty, 0 dddvalue, 0 tk from ipi_registration r where r.leave_dept_date >= to_date('2018-01-01 00:00:00', 'yyyy-MM-dd hh24:mi:ss') and r.leave_dept_date <= to_date('2018-06-30 00:00:00', 'yyyy-MM-dd hh24:mi:ss') and r.s_brztbh_dm <> '70' group by r.dept_id union ALL --------------- select a.dept_id, 0, 0, 0, sum(a.qty) qty, sum(a.dddvalue) dddvalue, SUM(a.tk) tk from (select h.ORDERED_DEPT_ID dept_id, sum(d.total_qty) qty, dd.id, dd.ddd_value, dd.single_dose_specification, dd.pharmacy_dose_scale, dd.ddd_convert_value, dd.DDD_VALUE, decode(dd.DDD_VALUE,0,null,(round(sum(d.total_qty) * dd.single_dose_specification * dd.pharmacy_dose_scale * DECODE(dd.ddd_convert_value, NULL, 1, dd.ddd_convert_value) / dd.DDD_VALUE,2))) dddvalue, (CASE WHEN dd.antibiotics_level = '20' THEN (decode(dd.DDD_VALUE,0,null, (round(sum(d.total_qty) * dd.single_dose_specification * dd.pharmacy_dose_scale * DECODE(dd.ddd_convert_value, NULL,1, dd.ddd_convert_value) / dd.DDD_VALUE, 2)))) ELSE NULL END) tk from ipi_registration r inner join ipc_drug_presc_h h on r.id = h.ipi_registration_id inner join ipc_drug_presc_d d on d.drug_presc_h_id = h.id inner join drm_dictionary dd on dd.id = d.drug_id where r.leave_dept_date >= to_date('2018-01-01 00:00:00', 'yyyy-MM-dd hh24:mi:ss') and r.leave_dept_date <= to_date('2018-06-30 23:59:59', 'yyyy-MM-dd hh24:mi:ss') and r.s_brztbh_dm <> '70' and dd.antibiotics_level is not null and dd.ddd_value is not null and dd.DRUG_COMMON_NAME not in ('复方磺胺甲恶唑片','注射用链霉素') group by h.ORDERED_DEPT_ID, dd.id, dd.ddd_value, dd.single_dose_specification, dd.pharmacy_dose_scale, dd.ddd_convert_value, dd.DDD_VALUE, dd.antibiotics_level) a group by a.dept_id) a inner join hra00_department dept on dept.id = a.dept_id group by dept.SORT_ORDER,dept.id, dept.department_chinese_name order by dept.SORT_ORDER,dept.department_chinese_name |
