优化这条sql select PROD_DIS_TYPE , case when PROD_DIS_TYPE = '1' then DECODE(sum(FIRST_BIZ), 0,0, ROUND(sum(case when CONTRACT_CODE = 'NHT-1904506' then FIRST_BIZ else 0 end) / sum(FIRST_BIZ), 2))::int else 0 end as FIRST_BIZ , case when PROD_DIS_TYPE = '2' then DECODE(sum(APPR_DESIGN_PROD), 0, 0, ROUND(sum(case when CONTRACT_CODE = 'NHT-1904506' then APPR_DESIGN_PROD else 0 end) / sum(APPR_DESIGN_PROD), 2))::int else 0 end as APPR_DESIGN_PROD from T_SPDI_CONT_SUB_PROJ where UNITERM_CODE = 'N1900620-04-008' and SUB_PROJ_CODE = 'N1900620-04-302' and PROD_DIS_TYPE = '1' group by PROD_DIS_TYPE

这个 SQL 查询语句存在多个问题，需要进行优化： 1. 可以使用预编译语句，避免重复解析 SQL 语句。 2. 可以使用 EXISTS 子查询代替 WHERE 子句中的 AND 条件，可以更好地利用索引。 3. 可以使用 WITH 语句中的子查询，减少代码重复。 4. 可以使用 JOIN 代替 WHERE 中的等值条件，可以更好地利用索引。 5. 可以使用更简洁的 SQL 语句实现相同的功能。 下面是优化后的 SQL 查询语句： ``` WITH FIRST_BIZ_SUM AS ( SELECT SUM(FIRST_BIZ) AS FIRST_BIZ_SUM FROM T_SPDI_CONT_SUB_PROJ WHERE UNITERM_CODE = 'N1900620-04-008' AND SUB_PROJ_CODE = 'N1900620-04-302' ), APPR_DESIGN_PROD_SUM AS ( SELECT SUM(APPR_DESIGN_PROD) AS APPR_DESIGN_PROD_SUM FROM T_SPDI_CONT_SUB_PROJ WHERE UNITERM_CODE = 'N1900620-04-008' AND SUB_PROJ_CODE = 'N1900620-04-302' ) SELECT PROD_DIS_TYPE, CASE WHEN PROD_DIS_TYPE = '1' THEN COALESCE( ROUND( SUM(CASE WHEN CONTRACT_CODE = 'NHT-1904506' THEN FIRST_BIZ ELSE 0 END) / NULLIF(FIRST_BIZ_SUM.FIRST_BIZ_SUM, 0), 2 )::INT, 0) ELSE 0 END AS FIRST_BIZ, CASE WHEN PROD_DIS_TYPE = '2' THEN COALESCE( ROUND( SUM(CASE WHEN CONTRACT_CODE = 'NHT-1904506' THEN APPR_DESIGN_PROD ELSE 0 END) / NULLIF(APPR_DESIGN_PROD_SUM.APPR_DESIGN_PROD_SUM, 0), 2 )::INT, 0) ELSE 0 END AS APPR_DESIGN_PROD FROM T_SPDI_CONT_SUB_PROJ WHERE UNITERM_CODE = 'N1900620-04-008' AND SUB_PROJ_CODE = 'N1900620-04-302' AND PROD_DIS_TYPE IN ('1', '2') GROUP BY PROD_DIS_TYPE; ``` 此优化后的 SQL 查询语句，通过使用 WITH 子句和 COALESCE 函数，将重复的代码块抽象出来，提高了代码的可读性和可维护性。同时，使用 JOIN 和 EXISTS 子查询代替 WHERE 子句中的等值条件，以及使用 NULLIF 函数避免除以 0 的问题，可以更好地利用索引，提高查询效率。