帮朋友优化一SQL---表重复全扫

最新推荐文章于 2024-09-14 21:52:32 发布
最新推荐文章于 2024-09-14 21:52:32 发布
阅读量684 收藏
点赞数
分类专栏： SQL优化 文章标签： 优化 date dictionary sql c
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wyan117/article/details/7825666
版权

 昨日，一朋友让我帮看个语句，原因是那个语句 写得过长，语句不容易理解，很多地方全表扫描了。。。。。

源SQL:

SELECT A.NAME,
      (SELECT COUNT(DISTINCT E.EXP_ID)
         FROM TBL_EDU_EXP E
         WHERE E.MAJOR_S = A.DICT_ID 
         AND trunc(E.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
         AND trunc(E.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') 
         AND E.SCHOOL_ID IN (2012060631583)) AS EX,
       (SELECT NVL((SUM(R.RECRUITMENT_NUMBER)), 0) 
         FROM TBL_RECRUITMENT_PROGRAM R 
         WHERE R.PROFESSIONAL_REQUIREMENTS = A.DICT_ID 
         AND R.STATUS=1 
         AND ((trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
         AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) 
         OR (trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') 
         AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) 
         OR (trunc(R.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
         AND trunc(R.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) 
         AND R.COMPANY_ID IN (2012022531438,2012031531465,2012020631316)) AS RE,
      ROW_NUMBER() OVER(ORDER BY ROUND((CASE WHEN (SELECT COUNT(DISTINCT E.EXP_ID) 
         FROM TBL_EDU_EXP E 
         WHERE E.MAJOR_S = A.DICT_ID 
         AND trunc(E.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
         AND trunc(E.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') 
         AND E.SCHOOL_ID IN (2012060631583)) > 
           (SELECT NVL((SUM(R.RECRUITMENT_NUMBER)), 0) 
              FROM TBL_RECRUITMENT_PROGRAM R 
              WHERE R.PROFESSIONAL_REQUIREMENTS = A.DICT_ID 
              AND R.STATUS=1 
              AND ((trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
              AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) 
              OR (trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') 
              AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) 
              OR (trunc(R.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
              AND trunc(R.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) 
              AND R.COMPANY_ID IN (2012022531438,2012031531465,2012020631316)) 
           THEN CASE WHEN 
               (SELECT NVL((SUM(R.RECRUITMENT_NUMBER)), 0) 
                   FROM TBL_RECRUITMENT_PROGRAM R 
                   WHERE R.PROFESSIONAL_REQUIREMENTS = A.DICT_ID 
                   AND R.STATUS=1 
                   AND ((trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
                   AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) 
                   OR (trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') 
                   AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) 
                   OR (trunc(R.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
                   AND trunc(R.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) 
                   AND R.COMPANY_ID IN (2012022531438,2012031531465,2012020631316)) = 0 
               THEN 
                   (SELECT COUNT(DISTINCT E.EXP_ID) 
                       FROM TBL_EDU_EXP E 
                       WHERE E.MAJOR_S = A.DICT_ID 
                       AND trunc(E.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
                       AND trunc(E.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') 
                       AND E.SCHOOL_ID IN (2012060631583)) 
               ELSE (SELECT COUNT(DISTINCT E.EXP_ID) 
                       FROM TBL_EDU_EXP E 
                       WHERE E.MAJOR_S = A.DICT_ID 
                       AND trunc(E.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
                       AND trunc(E.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') 
                       AND E.SCHOOL_ID IN (2012060631583)) / 
                       (SELECT NVL((SUM(R.RECRUITMENT_NUMBER)), 0) 
                           FROM TBL_RECRUITMENT_PROGRAM R 
                           WHERE R.PROFESSIONAL_REQUIREMENTS = A.DICT_ID 
                           AND R.STATUS=1 
                           AND ((trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
                           AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) 
                           OR (trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') 
                           AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) 
                           OR (trunc(R.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
                           AND trunc(R.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) 
                           AND R.COMPANY_ID IN (2012022531438,2012031531465,2012020631316)) 
                END ELSE CASE WHEN (SELECT COUNT(DISTINCT E.EXP_ID) 
                                       FROM TBL_EDU_EXP E 
                                       WHERE E.MAJOR_S = A.DICT_ID 
                                       AND trunc(E.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
                                       AND trunc(E.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') 
                                       AND E.SCHOOL_ID IN (2012060631583)) > 0 THEN 1 ELSE 0 END END),2) DESC,
                                         ROUND((CASE WHEN (SELECT COUNT(DISTINCT E.EXP_ID) 
                                                              FROM TBL_EDU_EXP E 
                                                              WHERE E.MAJOR_S = A.DICT_ID 
                                                              AND trunc(E.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
                                                              AND trunc(E.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') 
                                                              AND E.SCHOOL_ID IN (2012060631583)) 
                                                                  < (SELECT NVL((SUM(R.RECRUITMENT_NUMBER)), 0) 
                                                                       FROM TBL_RECRUITMENT_PROGRAM R 
                                                                       WHERE R.PROFESSIONAL_REQUIREMENTS = A.DICT_ID 
                                                                       AND R.STATUS=1 
                                                                       AND ((trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
                                                                       AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) 
                                                                       OR (trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') 
                                                                       AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) 
                                                                       OR (trunc(R.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
                                                                       AND trunc(R.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) 
                                                                       AND R.COMPANY_ID IN (2012022531438,2012031531465,2012020631316)) 
                                                   THEN CASE WHEN (SELECT COUNT(DISTINCT E.EXP_ID) 
                                                                     FROM TBL_EDU_EXP E 
                                                                     WHERE E.MAJOR_S = A.DICT_ID 
                                                                     AND trunc(E.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
                                                                     AND trunc(E.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') 
                                                                     AND E.SCHOOL_ID IN (2012060631583)) = 0 
                                                     THEN (SELECT NVL((SUM(R.RECRUITMENT_NUMBER)), 0) 
                                                              FROM TBL_RECRUITMENT_PROGRAM R 
                                                              WHERE R.PROFESSIONAL_REQUIREMENTS = A.DICT_ID 
                                                              AND R.STATUS=1 
                                                              AND ((trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
                                                              AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) 
                                                              OR (trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD')
                                                              AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) 
                                                              OR (trunc(R.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
                                                              AND trunc(R.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) 
                                                              AND R.COMPANY_ID IN (2012022531438,2012031531465,2012020631316))
                                                     ELSE (SELECT NVL((SUM(R.RECRUITMENT_NUMBER)), 0) 
                                                              FROM TBL_RECRUITMENT_PROGRAM R 
                                                              WHERE R.PROFESSIONAL_REQUIREMENTS = A.DICT_ID 
                                                              AND R.STATUS=1 
                                                              AND ((trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
                                                              AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) 
                                                              OR (trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') 
                                                              AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) 
                                                              OR (trunc(R.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')
                                                              AND trunc(R.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) 
                                                              AND R.COMPANY_ID IN 
                                                              (2012022531438,2012031531465,2012020631316)) / (SELECT COUNT(DISTINCT E.EXP_ID) 
                                                                                                                FROM TBL_EDU_EXP E 
                                                                                                                WHERE E.MAJOR_S = A.DICT_ID 
                                                                                                                AND trunc(E.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
                                                                                                                AND trunc(E.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') 
                                                                                                                AND E.SCHOOL_ID IN (2012060631583)) 
                                                                                   END ELSE CASE WHEN (SELECT NVL((SUM(R.RECRUITMENT_NUMBER)), 0) 
                                                                                                           FROM TBL_RECRUITMENT_PROGRAM R 
                                                                                                           WHERE R.PROFESSIONAL_REQUIREMENTS = A.DICT_ID 
                                                                                                           AND R.STATUS=1 
                                                                                                           AND ((trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
                                                                                                           AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) 
                                                                                                           OR (trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') 
                                                                                                           AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) 
                                                                                                           OR (trunc(R.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
                                                                                                           AND trunc(R.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) 
                                                                                                           AND R.COMPANY_ID IN (2012022531438,2012031531465,2012020631316)) > 0 
                                                                                    THEN 1 ELSE 0 END END),2) ASC) RN 
 FROM TBL_DICTIONARY A, TBL_EDU_EXP B, TBL_RECRUITMENT_PROGRAM C
  WHERE A.TYPE_ID = 018 
  AND ((A.DICT_ID = B.MAJOR_S AND trunc(B.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
  AND trunc(B.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND B.SCHOOL_ID IN (2012060631583)) 
  OR (A.DICT_ID = C.PROFESSIONAL_REQUIREMENTS AND C.STATUS=1 
  AND ((trunc(C.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
  AND trunc(C.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) 
  OR (trunc(C.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') 
  AND trunc(C.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) 
  OR (trunc(C.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
  AND trunc(C.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) 
  AND C.COMPANY_ID IN (2012022531438,2012031531465,2012020631316)))
 GROUP BY A.NAME,A.DICT_ID

源SQL的执行计划为：


源SQL执行计划图标

其中表的数据量为：

select count(1) from  tbl_edu_exp   --1929  rows  全扫 9次
 
select count(1) from  tbl_recruitment_program   --228  rows  全扫8次

select count(1） from  tbl_dictionary    --2697 rows


分析：

-------------------------------------------------------------------------------------------------------

从执行计划中看的出有2张表（  tbl_edu_exp   、 tbl_recruitment_program  ）多次重复全扫。

再看SQL语句，3张表连接，语句中重复出现的片断为：


SELECT NVL((SUM(R.RECRUITMENT_NUMBER)), 0) 
              FROM TBL_RECRUITMENT_PROGRAM R 
              WHERE R.PROFESSIONAL_REQUIREMENTS = A.DICT_ID 
              AND R.STATUS=1 
              AND ((trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
              AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) 
              OR (trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') 
              AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) 
              OR (trunc(R.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
              AND trunc(R.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) 
              AND R.COMPANY_ID IN (2012022531438,2012031531465,2012020631316)

SELECT COUNT(DISTINCT E.EXP_ID) 
                       FROM TBL_EDU_EXP E 
                       WHERE E.MAJOR_S = A.DICT_ID 
                       AND trunc(E.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
                       AND trunc(E.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') 
                       AND E.SCHOOL_ID IN (2012060631583)

于是准备把以上这2片断的结果集当做一个表用with ..as方式来处理，处理如下： 

with sr as
(SELECT a.dict_id,NVL((SUM(R.RECRUITMENT_NUMBER)), 0) as sum_recruitment
              FROM TBL_RECRUITMENT_PROGRAM R ,TBL_DICTIONARY A
              WHERE R.PROFESSIONAL_REQUIREMENTS = A.DICT_ID 
              AND R.STATUS=1 
              AND ((trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
              AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) 
              OR (trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') 
              AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) 
              OR (trunc(R.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
              AND trunc(R.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) 
              AND R.COMPANY_ID IN (2012022531438,2012031531465,2012020631316)
			  and A.TYPE_ID = 018 
			  group by a.dict_id
			  )
,ep as 
(
SELECT a.dict_id,COUNT(DISTINCT E.EXP_ID) as expidcount
         FROM TBL_EDU_EXP E,TBL_DICTIONARY A
         WHERE E.MAJOR_S = A.DICT_ID 
         AND trunc(E.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
         AND trunc(E.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') 
         AND E.SCHOOL_ID IN (2012060631583)
		 and A.TYPE_ID = 018
		 group by a.dict_id
		 )


注意 ：SQL语句where条件中的逻辑结构是：

 WHERE A.TYPE_ID = 018 
  AND (
  (A.DICT_ID = B.MAJOR_S AND trunc(B.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
  AND trunc(B.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND B.SCHOOL_ID IN (2012060631583)) 
  
  OR 
  (A.DICT_ID = C.PROFESSIONAL_REQUIREMENTS AND C.STATUS=1 
  AND ((trunc(C.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
  AND trunc(C.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) 
  OR (trunc(C.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') 
  AND trunc(C.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) 
  OR (trunc(C.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
  AND trunc(C.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) 
  AND C.COMPANY_ID IN (2012022531438,2012031531465,2012020631316))
  )
 GROUP BY A.NAME,A.DICT_ID 

中间有个or ，空行的下面


所以在where 部分要用union all来连接 with as的2个表。

最终调整：

-----------------------------------------------------------------------

with .. as改写语句：

with sr as
(SELECT a.dict_id,NVL((SUM(R.RECRUITMENT_NUMBER)), 0) as sum_recruitment,0 expidcount
              FROM TBL_RECRUITMENT_PROGRAM R ,TBL_DICTIONARY A
              WHERE R.PROFESSIONAL_REQUIREMENTS = A.DICT_ID 
              AND R.STATUS=1 
              AND ((trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
              AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) 
              OR (trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') 
              AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) 
              OR (trunc(R.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
              AND trunc(R.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) 
              AND R.COMPANY_ID IN (2012022531438,2012031531465,2012020631316)
              and A.TYPE_ID = 018
              group by a.dict_id
        )
,ep as 
(
SELECT a.dict_id,0 as sum_recruitment,COUNT(DISTINCT E.EXP_ID) as expidcount
         FROM TBL_EDU_EXP E,TBL_DICTIONARY A
         WHERE E.MAJOR_S = A.DICT_ID 
         AND trunc(E.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
         AND trunc(E.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') 
         AND E.SCHOOL_ID IN (2012060631583)
         and A.TYPE_ID = 018
         group by a.dict_id
     )    
SELECT A.NAME,
      (sum(b.expidcount)) AS ex,
      (sum(b.sum_recruitment)) AS re,
      ROW_NUMBER() OVER(ORDER BY 
    ROUND((CASE WHEN (sum(b.expidcount)) > 
           sum((b.sum_recruitment)) 
           THEN CASE WHEN sum((b.sum_recruitment))=0
       then sum(b.expidcount)
       else sum(b.expidcount)/sum((b.sum_recruitment))
              end else
       CASE WHEN (sum(b.expidcount)) > 0 
       THEN 1 ELSE 0 END 
       END),2) DESC,
  ROUND((CASE WHEN (sum(b.expidcount)) < (sum((b.sum_recruitment))) 
     THEN CASE WHEN (sum(b.expidcount)) = 0 
                                                     THEN (sum((b.sum_recruitment)))
                                                     ELSE (sum((b.sum_recruitment))) / (sum(b.expidcount)) 
                                                     END ELSE 
    CASE WHEN (sum((b.sum_recruitment))) > 0 
              THEN 1 ELSE 0 END END),2) ASC) RN 
 FROM TBL_DICTIONARY A ,(select * from sr
        union all 
        select * from ep) b
 where  A.TYPE_ID = 018 and
 a.dict_id=b.dict_id
 GROUP BY A.NAME,A.DICT_ID


因朋友程序原因不能用with ..as来改写，最终调整改写如下：

SELECT A.NAME,
      (sum(b.expidcount)) AS ex,
      (sum(b.sum_recruitment)) AS re,
      ROW_NUMBER() OVER(ORDER BY 
    ROUND((CASE WHEN (sum(b.expidcount)) > 
           sum((b.sum_recruitment)) 
           THEN CASE WHEN sum((b.sum_recruitment))=0
       then sum(b.expidcount)
       else sum(b.expidcount)/sum((b.sum_recruitment))
              end else
       CASE WHEN (sum(b.expidcount)) > 0 
       THEN 1 ELSE 0 END 
       END),2) DESC,
  ROUND((CASE WHEN (sum(b.expidcount)) < (sum((b.sum_recruitment))) 
     THEN CASE WHEN (sum(b.expidcount)) = 0 
                                                     THEN (sum((b.sum_recruitment)))
                                                     ELSE (sum((b.sum_recruitment))) / (sum(b.expidcount)) 
                                                     END ELSE 
    CASE WHEN (sum((b.sum_recruitment))) > 0 
              THEN 1 ELSE 0 END END),2) ASC) RN 
FROM 
(SELECT a.dict_id,NVL((SUM(R.RECRUITMENT_NUMBER)), 0) as sum_recruitment,0 expidcount
              FROM TBL_RECRUITMENT_PROGRAM R ,TBL_DICTIONARY A
              WHERE R.PROFESSIONAL_REQUIREMENTS = A.DICT_ID 
              AND R.STATUS=1 
              AND ((trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
              AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) 
              OR (trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') 
              AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) 
              OR (trunc(R.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
              AND trunc(R.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) 
              AND R.COMPANY_ID IN (2012022531438,2012031531465,2012020631316)
              and A.TYPE_ID = 018
              group by a.dict_id
        
union all 

SELECT a.dict_id,0 as sum_recruitment,COUNT(DISTINCT E.EXP_ID) as expidcount
         FROM TBL_EDU_EXP E,TBL_DICTIONARY A
         WHERE E.MAJOR_S = A.DICT_ID 
         AND trunc(E.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') 
         AND trunc(E.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') 
         AND E.SCHOOL_ID IN (2012060631583)
         and A.TYPE_ID = 018
         group by a.dict_id
    ) b ,    
TBL_DICTIONARY A 
 where  A.TYPE_ID = 018 and
 a.dict_id=b.dict_id
 GROUP BY A.NAME,A.DICT_ID

以上改写SQL的执行计划为：

调整SQL后的执行计划


本次SQL优化，看下源SQL的执行计划然立马能定位到问题所在，再看下SQL的写法，很快就能确定解决方法了。



                

        

        

    

  


    

      

        

            

              
                
                  wyan117
                
              
            

            

                关注
              
            

        

        

          
      

      

            

                专栏目录
          









                



	

		

			

				
					
慢查询优化方案-SQL篇(1)

				
			

			

				

					2401_83411843的博客
				

				

					04-21
					
					872
					
				

			

		

		

			
				
Java架构进阶面试及知识点文档笔记这份文档共498页，其中包括Java集合，并发编程，JVM，Dubbo,Redis，Spring全家桶，MySQL，Kafka等面试解析及知识点整理Java分布式高级面试问题解析文档其中都是包括分布式的面试问题解析，内容有分布式消息队列，Redis缓存，分库分表，微服务架构，分布式高可用，读写分离等等！互联网Java程序员面试必备问题解析及文档学习笔记Java架构进阶视频解析合集《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》

			
		

	



                

              
                



	

		

			

				
					
（2022年最新版本）SQL优化最杆货总结 - MySQL

				
			

			

				

					m0_68850571的博客
				

				

					04-12
					
					533
					
				

			

		

		

			
				
前言

BATJTMD等大厂的面试难度越来越高，但无论从大厂还是到小公司，一直未变的一个重点就是对SQL优化经验的考察。一提到数据库，先“说一说你对SQL优化的见解吧？”。SQL优化已经成为衡量程序猿优秀与否的硬性指标，甚至在各大厂招聘岗位职能上都有明码标注，如果是你，在这个问题上能吊打面试官还是会被吊打呢？





（注:如果看着模糊，可能是你撸多了）

有朋友疑问到，SQL优化真的有这么重要么？如下图所示，SQL优化在提升系统性能中是：（成本最低 && 优化效果最明显） 的途径。如果你

			
		

	



                


  
              

                


	

		

			

				
					
SQL Server 2005 扫描计数产生原因分析(1）

				
			

			

				

					weixin_33738555的博客
				

				

					07-22
					
					94
					
				

			

		

		

			
				
在上次做分页分析是分析时，执行下面语句：
SELECTTOP19*
FROM
[dbo].[[[zping.com]]]]]
WHERE
(IDNOTIN(SELECTTOP(10*(10000-1))IDFROM[dbo].[[[zping.com]]]]]ORDERBYIDDESC))
ORDERBY
IDDESC
 ...

			
		

	





	

		

			

				
					
MYSQL 查询优化之路-之DISTINCT全表扫描

				
			

			

				

					weixin_33675507的博客
				

				

					03-28
					
					1082
					
				

			

		

		

			
				
2019独角兽企业重金招聘Python工程师标准>>>   
                                        
         ...

			
		

	



		

			
		



	

		

			

				
					
一张表多次扫描时可以用case语句合并

				
			

			

				

					xxydzyr的博客
				

				

					01-18
					
					458
					
				

			

		

		

			
				
一张表多次扫描时可以用case语句合并
概述：
当我们发现有一些语句比较相似的时候，可以考虑使用一个语句进行实现，避免多次扫描相同的表。
举例：
select count(1) from t_testuser where userid &lt;1000;
select count(1) from t_testuser where userid between 1000 and 5000;
sele...

			
		

	





	

		

			

				
					
mysql查询结果为0行_SQL中如何显示COUNT结果为0的行

				
			

			

				

					weixin_36212334的博客
				

				

					02-02
					
					2215
					
				

			

		

		

			
				
展开全部SELECTModelType．ModelType，t1．count1FROMModelTypeLEFTOUTERJOIN(SELECTModelTypeID，COUNT(＊)ascount1FROMMobilesGROUPBYModelTypeID)t1ONt1．ModelTypeID＝ModelType．ModelTypeID注意：e69da5e6ba9062616964757a686...

			
		

	





	

		

			

				
					
SQL优化最干货总结 - MySQL（2020最新版）

					
热门推荐

				
			

			

				

					陈哈哈的菜园子
				

				

					06-29
					
					76万+
					
				

			

		

		

			
				
MySQL - SQL优化干货总结（吐血版），别辜负了自己的梦想，欢迎白嫖、点赞、收藏。

			
		

	





	

		

			

				
					
SQL优化最干货总结-MySQL(2021最新版）

				
			

			

				

					hello world！
				

				

					01-26
					
					523
					
				

			

		

		

			
				
点击上方蓝色“java大数据修炼之道”,选择“设为星标”每晚九点: 技术干货 ???? 必定奉上作者：陈哈哈chensj.blog.csdn.net/article/details/1...

			
		

	





	

		

			

				
					
字节总监首发“2021年SQL优化最干货总结 - MySQL”的成长笔记！GitHub公开半小时获11K标星

				
			

			

				

					XX15218893246的博客
				

				

					06-04
					
					211
					
				

			

		

		

			
				
前言
网上关于SQL优化的教程很多，但是比较杂乱。而且百度字节等大厂的面试难度越来越高，无论从大厂还是到小公司，一直未变的一个重点就是对SQL优化经验的考察。一提到数据库，先“说一说你对SQL优化的见解吧？”。SQL优化已经成为衡量程序猿优秀与否的硬性指标，甚至在各大厂招聘岗位职能上都有明码标注，所以近日有空我专门花费了大量的时间查找资料、修改、排版，希望能够帮助到有需要的朋友，感觉好的话推荐给更多的人，让更多的人看到、纠正以及补充。
有朋友疑问到，SQL优化真的有这么重要么？如下图所示，SQL优化在提升系

			
		

	





	

		

			

				
					
MySQL数据库SQL语句执行顺序及优化

				
			

			

				

					Damon_zqt的博客
				

				

					05-11
					
					1030
					
				

			

		

		

			
				
一、sql执行顺序
(1)from
(3) join
(2) on
(4) where
(5)group by(开始使用select中的别名，后面的语句中都可以使用)
(6) avg,sum…
(7)having
(8) select
(9) distinct
(10) order by
(11) limit
从这个顺序中我们不难发现，所有的 查询语句都是从from开始执行的，在执行过程中，每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入。
第一步：首先对from子句中的前两个表

			
		

	





	

		

			

				
					
SQL优化最干货总结（2021最新版）

				
			

			

				

					这是随心创作的博主
				

				

					06-18
					
					414
					
				

			

		

		

			
				
前言

SELECT语句 - 语法顺序：

SELECT语句 - 执行顺序：

SQL优化策略

一、避免不走索引的场景

二、SELECT语句其他优化

三、增删改 DML 语句优化

四、查询条件优化

五、建表优化

有朋友疑问到，SQL优化真的有这么重要么？如下图所示，SQL优化在提升系统性能中是：（成本最低 && 优化效果最明显） 的途径。如果你的团队在SQL优化这方面搞得很优秀，对你们整个大型系统可用性方面无疑是一个质的跨越，真的能让你们老板省下不止几沓子钱。




	优化成本

			
		

	





	

		

			

				
					
oracle nvl count 1 0,【mysql】返回count(*)总数为0的值

				
			

			

				

					weixin_39625872的博客
				

				

					04-09
					
					400
					
				

			

		

		

			
				
【mysql】返回count(*)总数为0的值在做mysql题的时候遇到了比较棘手的问题，花费了挺长时间才解决(本人小白)，难点应该就是这个：返回count(*)是0的值(原题地址：https://leetcode-cn.com/problems/trips-and-users/submissions/)。太多知识点不会了，做的时候很掉头发！在网上查了资料,一步一步解决，最终Sql如下(用Navi...

			
		

	





	

		

			

				
					
navicate远程linux上的pgsql提示密码失败

				
			

			

				

					记录、分享、合作、共赢
				

				

					09-14
					
					353
					
				

			

		

		

			
				
2、postgresql.conf文件中，修改listen_addresses。3、重启pgsql，例如：systemctl restart pgsql。1、pg_hba.conf文件中，ipv4下面的内容改成。4、如果问题还在，检查firewalld防火墙，执行。5、再次尝试连接，成功！

			
		

	





	

		

			

				
					
mysql学习教程，从入门到精通，SQL ORDER BY 子句（14）

					
最新发布

				
			

			

				

					qq_45746668的博客
				

				

					09-14
					
					861
					
				

			

		

		

			
				
通常，当您使用SELECT语句从表中获取数据时，结果集中的行没有任何特定的顺序。如果要按特定顺序排列结果集，则可以在语句末尾指定ORDER BY子句，该子句告诉程序如何对查询返回的数据进行排序。默认排序顺序为升序。

			
		

	





	

		

			

				
					
经典sql题（一）求连续登录不少于三天用户

				
			

			

				

					m0_58076578的博客
				

				

					09-14
					
					1101
					
				

			

		

		

			
				
去重提取日期查询：提取每个用户的唯一登录日期。结果表table1：显示每个用户的唯一日期。为每个用户生成序列号查询：为每个用户的日期生成序号，并计算date2。结果表table2：显示用户的日期和对应的date2。步骤使用函数，按照日期顺序为每个用户的登录日期分配一个序号。例如，对于用户 ID = 1，假设他们的日期是2023-10-012023-10-02和2023-10-03将为它们生成序号 1、2 和 3。date2是通过将每个日期减去它的序号得到的。这个操作的目的是为了检测连续的日期。

			
		

	





	

		

			

				
					
网络安全 DVWA通关指南 DVWA SQL Injection (Blind SQL盲注)

				
			

			

				

					YueXuan_521的博客
				

				

					09-14
					
					1086
					
				

			

		

		

			
				
这篇文章是关于网络安全DVWA（Damn Vulnerable Web Application）的SQL注入挑战指南，主要介绍了Low、Medium、High和Impossible四个安全级别的SQL盲注攻击方法。Low级别通过直接构造SQL语句获取数据库信息；Medium级别使用ASCII码值绕过单引号转义；High级别使用布尔盲注和Cookie传参，并有限制查询结果；Impossible级别通过参数化查询和CSRF令牌防御SQL注入。文章详细展示了攻击步骤和防御措施，帮助读者理解

			
		

	





	

		

			

				
					
JAVA与SQL-Server图书管理系统设计与开发报告

				
			

			

				

					
				

			

		

		

			
				
本篇文档是关于《数据库原理与应用》课程设计的报告，主题是基于Java和SQL Server的图书管理系统设计与开发。这份报告由一名学生在20XX年11月30日至12月18日期间完成，针对的是计算机科学与工程专业的学生。设计任务...

			
		

	



              




          




        



    





    



      

      

        
      

      





	

		评论	
	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值