怎么优化一下这段语句

Question

我应该怎么优化一下这段语句呢

select fun_get_dept_name(t.RECIPE_DEPTCODE) as 科室,
       sum(t.own_cost) as 总金额,
       (select sum(a.TOT_COST) as 药品费
          from fin_ipb_feeinfo a
         where a.fee_code in ('01', '130', '131')
           and a.Balance_Date >=
               to_date('2023-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
           and a.balance_date <=
               to_date('2023-04-30 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
           and t.recipe_deptcode = a.recipe_deptcode
         group by a.recipe_deptcode) as 药品费,
       round((select sum(a.TOT_COST) as 药品费
                from fin_ipb_feeinfo a
               where a.fee_code in ('01', '130', '131')
                 and a.Balance_Date >=
                     to_date('2023-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
                 and a.balance_date <=
                     to_date('2023-04-30 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
                 and t.recipe_deptcode = a.recipe_deptcode
               group by a.recipe_deptcode) / sum(t.own_cost) * 100,
             2) as 药占比
  from fin_ipb_feeinfo t
 where t.Balance_Date >=
       to_date('2023-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
   and t.balance_date <=
       to_date('2023-04-30 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
 group by t.RECIPE_DEPTCODE

Answer 1

试试这样呢

WITH drug_cost AS (
  SELECT a.recipe_deptcode, SUM(a.TOT_COST) AS drug_cost
  FROM fin_ipb_feeinfo a
  WHERE a.fee_code IN ('01', '130', '131')
    AND a.Balance_Date >= TO_DATE('2023-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
    AND a.balance_date <= TO_DATE('2023-04-30 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
  GROUP BY a.recipe_deptcode
)
SELECT fun_get_dept_name(t.RECIPE_DEPTCODE) AS 科室,
       SUM(t.own_cost) AS 总金额,
       d.drug_cost AS 药品费,
       ROUND(d.drug_cost / SUM(t.own_cost) * 100, 2) AS 药占比
FROM fin_ipb_feeinfo t
LEFT JOIN drug_cost d ON t.recipe_deptcode = d.recipe_deptcode
WHERE t.Balance_Date >= TO_DATE('2023-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
  AND t.balance_date <= TO_DATE('2023-04-30 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
GROUP BY t.RECIPE_DEPTCODE;