优化sql。select sum(case when c.id_Dep_Pi_Af = '60877de521997e2901a929a1' and c.sd_Apply_Ap_Cd in ('1', '5') then 1 else 0 end) as depPiInNum, SUM(DECODE(c.ID_DEP_PI_BEF, '60877de521997e2901a929a1', 1, 0)) as depPiOutNum from Hi_Vis_Med a left join Hi_Vis_Med_Ip b on a.id_Vismed = b.id_Vismed left join Hi_Vis_Med_Dep_Pi_Changeap c on b.id_Vismed = c.id_Vismed where a.sd_Vistp_Cd = '114' and a.delete_Flag = 0 and c.sd_Apply_Ap_Cd in ('1', '2', '5', '6');

可以尝试以下几个优化方式：
1. 使用索引：对于经常使用的列，可以创建索引，如sd_Vistp_Cd、delete_Flag、id_Dep_Pi_Af、sd_Apply_Ap_Cd等。
2. 使用EXISTS代替LEFT JOIN：如果不需要返回左侧表中所有记录，可以使用EXISTS代替LEFT JOIN，可以提高查询效率。
3. 单表过滤放在连接表后面：在多表连接查询中，应该先连接再过滤，将单表过滤条件放在连接表后面，可以避免对连接表进行无效的连接操作。
4. 尽量避免使用函数：在查询中尽量避免使用函数，因为函数的计算是比较耗时的，可以考虑通过重构SQL语句，将函数转化为其他方式实现。

相关问题

select sum(case when c.id_Dep_Pi_Af = '60877de521997e2901a929a1' and c.sd_Apply_Ap_Cd in ('1', '5') then 1 else 0 end) as depPiInNum, SUM(DECODE(c.ID_DEP_PI_BEF, '60877de521997e2901a929a1', 1, 0)) as depPiOutNum from Hi_Vis_Med a left join Hi_Vis_Med_Ip b on a.id_Vismed = b.id_Vismed left join Hi_Vis_Med_Dep_Pi_Changeap c on b.id_Vismed = c.id_Vismed where a.sd_Vistp_Cd = '114' and a.delete_Flag = 0 and c.sd_Apply_Ap_Cd in ('1', '2', '5', '6'); sql优化

可以尝试以下优化方式：
1. 创建索引：可以在Hi_Vis_Med、Hi_Vis_Med_Ip、Hi_Vis_Med_Dep_Pi_Changeap表中分别创建以下列的索引：id_Vismed、sd_Vistp_Cd、delete_Flag、id_Dep_Pi_Af、sd_Apply_Ap_Cd、ID_DEP_PI_BEF等。
2. 使用EXISTS代替LEFT JOIN：可以将LEFT JOIN改为EXISTS，如下所示：
select sum(case when c.id_Dep_Pi_Af = '60877de521997e2901a929a1' and c.sd_Apply_Ap_Cd in ('1', '5') then 1 else 0 end) as depPiInNum,
SUM(DECODE(c.ID_DEP_PI_BEF, '60877de521997e2901a929a1', 1, 0)) as depPiOutNum
from Hi_Vis_Med a
where a.sd_Vistp_Cd = '114' and a.delete_Flag = 0
and exists (
select 1 from Hi_Vis_Med_Ip b, Hi_Vis_Med_Dep_Pi_Changeap c
where a.id_Vismed = b.id_Vismed and b.id_Vismed = c.id_Vismed and c.sd_Apply_Ap_Cd in ('1', '2', '5', '6')
);
3. 将DECODE函数转成CASE语句：可以将DECODE函数转成CASE语句，如下所示：
select sum(case when c.id_Dep_Pi_Af = '60877de521997e2901a929a1' and c.sd_Apply_Ap_Cd in ('1', '5') then 1 else 0 end) as depPiInNum,
SUM(CASE WHEN c.ID_DEP_PI_BEF = '60877de521997e2901a929a1' THEN 1 ELSE 0 END) as depPiOutNum
from Hi_Vis_Med a
where a.sd_Vistp_Cd = '114' and a.delete_Flag = 0
and exists (
select 1 from Hi_Vis_Med_Ip b, Hi_Vis_Med_Dep_Pi_Changeap c
where a.id_Vismed = b.id_Vismed and b.id_Vismed = c.id_Vismed and c.sd_Apply_Ap_Cd in ('1', '2', '5', '6')
);

帮我优化这个sql select sum(case when b.id_Dep_Pi_Out = '60877de521997e2901a929a1' and b.SD_CHANGE_CD in ('1', '6') and (b.id_Org_In = b.id_Org or b.id_Org_In is null) then 1 else 0 end) depOutNum, SUM(case when b.SD_CHANGE_CD in '2' and (b.id_Org_In = b.id_Org or b.id_Org_In is null) and b.id_Dep_In in ('6045e4ffd0081238ab9c034f', '6045e517d0081238ab9c1d6d', '60d29a646f58d164c48a29fa', '60877e4d21997e2901a983de', '60866c4221997e105b5f3ea2', '60877e6821997e2901a9981e', '6045e46dd0081238ab9ba5c0', '6440d7f893bb71aa979322dc', '6045e57ad0081238ab9c6225') and (b.id_Dep_Pi_In = '60877de521997e2901a929a1' or b.id_Dep_Pi_In is null) then 1 else 0 end) depInNum, SUM(case when b.SD_CHANGE_CD in '2' and b.id_Org_In != b.id_Org and b.id_Dep_In in ('6045e4ffd0081238ab9c034f', '6045e517d0081238ab9c1d6d', '60d29a646f58d164c48a29fa', '60877e4d21997e2901a983de', '60866c4221997e105b5f3ea2', '60877e6821997e2901a9981e', '6045e46dd0081238ab9ba5c0', '6440d7f893bb71aa979322dc', '6045e57ad0081238ab9c6225') and (b.id_Dep_Pi_In = '60877de521997e2901a929a1' or b.id_Dep_Pi_In is null) then 1 else 0 end) orgDepInNum from Hi_Hpl_Med_Ord a join Hi_Hpl_Med_Ord_Chndep b on a.id_Medord = b.id_Medord where b.SD_CHANGE_CD in ('2','1', '6') and a.delete_Flag = '0' and a.sd_Medordsta_Cd in '31' and b.id_Org = '6045d1b8d0081238ab92050a'

这个SQL语句的主要性能瓶颈在于三个SUM函数，每个SUM函数都包含了很复杂的CASE语句，而且这些CASE语句中还有很多重复的条件判断。为了优化这个SQL语句，可以考虑以下几个方面：

1. 尽量避免复杂的CASE语句。可以通过使用JOIN子句来筛选需要的数据，然后再对这些数据进行SUM操作。

2. 尽量避免重复的条件判断。可以将一些重复的条件抽象出来，放在WHERE子句中，避免在CASE语句中重复判断。

3. 尽量避免使用OR条件。OR条件会导致查询优化器无法使用索引，从而导致查询性能下降。可以将OR条件转换为IN条件来优化查询。

4. 尽量避免在WHERE子句中使用函数或表达式。这些操作会导致查询优化器无法使用索引，从而导致查询性能下降。

基于以上优化原则，可以重写这个SQL语句如下：

SELECT SUM(depOutNum) AS depOutNum,
       SUM(depInNum) AS depInNum,
       SUM(orgDepInNum) AS orgDepInNum
FROM (
  SELECT CASE WHEN b.id_Dep_Pi_Out = '60877de521997e2901a929a1'
                  AND b.SD_CHANGE_CD IN ('1', '6')
                  AND (b.id_Org_In = b.id_Org OR b.id_Org_In IS NULL)
             THEN 1
             ELSE 0
        END AS depOutNum,
        CASE WHEN b.SD_CHANGE_CD IN ('2', '1', '6')
                  AND (b.id_Org_In = b.id_Org OR b.id_Org_In IS NULL)
                  AND b.id_Dep_In IN ('6045e4ffd0081238ab9c034f', '6045e517d0081238ab9c1d6d',
                                       '60d29a646f58d164c48a29fa', '60877e4d21997e2901a983de',
                                       '60866c4221997e105b5f3ea2', '60877e6821997e2901a9981e',
                                       '6045e46dd0081238ab9ba5c0', '6440d7f893bb71aa979322dc',
                                       '6045e57ad0081238ab9c6225')
                  AND (b.id_Dep_Pi_In = '60877de521997e2901a929a1' OR b.id_Dep_Pi_In IS NULL)
             THEN 1
             ELSE 0
        END AS depInNum,
        CASE WHEN b.SD_CHANGE_CD IN ('2', '1', '6')
                  AND b.id_Org_In != b.id_Org
                  AND b.id_Dep_In IN ('6045e4ffd0081238ab9c034f', '6045e517d0081238ab9c1d6d',
                                       '60d29a646f58d164c48a29fa', '60877e4d21997e2901a983de',
                                       '60866c4221997e105b5f3ea2', '60877e6821997e2901a9981e',
                                       '6045e46dd0081238ab9ba5c0', '6440d7f893bb71aa979322dc',
                                       '6045e57ad0081238ab9c6225')
                  AND (b.id_Dep_Pi_In = '60877de521997e2901a929a1' OR b.id_Dep_Pi_In IS NULL)
             THEN 1
             ELSE 0
        END AS orgDepInNum
  FROM Hi_Hpl_Med_Ord a
  JOIN Hi_Hpl_Med_Ord_Chndep b ON a.id_Medord = b.id_Medord
  WHERE b.SD_CHANGE_CD IN ('2', '1', '6')
    AND a.delete_Flag = '0'
    AND a.sd_Medordsta_Cd IN ('31')
    AND b.id_Org = '6045d1b8d0081238ab92050a'
    AND b.id_Dep_In IN ('6045e4ffd0081238ab9c034f', '6045e517d0081238ab9c1d6d',
                         '60d29a646f58d164c48a29fa', '60877e4d21997e2901a983de',
                         '60866c4221997e105b5f3ea2', '60877e6821997e2901a9981e',
                         '6045e46dd0081238ab9ba5c0', '6440d7f893bb71aa979322dc',
                         '6045e57ad0081238ab9c6225')
)

这个重写后的SQL语句遵循了以上的优化原则，通过使用子查询和简化的CASE语句来实现了相同的功能，并且可以更好地利用索引来提高查询的性能。