none
SQL优化 RRS feed

  • 问题

  •   SELECT V.C3,
                                V.CA6,
                                V.PID,
                                V.YEAR1,
                                V.DJ1 / C.DELIVERYNUMBER AS INSTALLRATE
                         FROM   ((SELECT C3,
                                        C6,
                                        YEAR1,
                                        ID,
                                        SUM(DJ1) AS DJ1
                                 FROM   A6
                                 GROUP  BY C3,
                                           C6,
                                           YEAR1,
                                           ID) V
                                          
                                 JOIN (SELECT C3,
                                              C6,
                                              YEAR1,
                                              SUM(DJ2) AS DJ2
                                       FROM   A4
                                       GROUP  BY CA3,
                                                 CA6,
                                                 YEAR1) C
                                   ON V.C3 = C.C3
                                      AND V.C6 = C.C6
                                      AND V.YEAR1 = C.YEAR1 )
    2012年7月6日 0:48

答案

  • 优化的话,需要还是考虑组合索引吧。

    ;with t1 as 
    (	SELECT C3,C6,YEAR1,ID,SUM(DJ1) AS DJ1
    	FROM   A6
    	GROUP  BY C3, C6, YEAR1, ID
    ),t2 as
    (	SELECT C3, C6, YEAR1, SUM(DJ2) AS DJ2
    	FROM   A4
    	GROUP  BY CA3, CA6, YEAR1)
    select t1.*,t1.DJ1/t2.DJ2 as INSTALLRATE from t1 
    join t2
    on t1.C3 = t2.C3 AND t1.C6 = t2.C6 AND t1.YEAR1 = t2.YEAR1 

    2012年7月9日 3:30
    版主

全部回复

  • 写错了!

     SELECT V.C3,
                                V.CA6,
                                V.PID,
                                V.YEAR1,
                                V.DJ1 / C.DJ2 AS INSTALLRATE
                         FROM   ((SELECT C3,
                                        C6,
                                        YEAR1,
                                        ID,
                                        SUM(DJ1) AS DJ1
                                 FROM   A6
                                 GROUP  BY C3,
                                           C6,
                                           YEAR1,
                                           ID) V
                                          
                                 JOIN (SELECT C3,
                                              C6,
                                              YEAR1,
                                              SUM(DJ2) AS DJ2
                                       FROM   A4
                                       GROUP  BY CA3,
                                                 CA6,
                                                 YEAR1) C
                                   ON V.C3 = C.C3
                                      AND V.C6 = C.C6
                                      AND V.YEAR1 = C.YEAR1 )

    2012年7月6日 0:58
  • 单独的语句很难优化的,要知道表结构,数据才好做。

    想不想时已是想,不如不想都不想。

    2012年7月6日 5:53
    版主
  • 你好,

    请提供我们具体的标结构、数据和所需要的结果,这样能更好的帮助你解决你的问题。

    Thanks.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    2012年7月9日 2:37
    版主
  • 优化的话,需要还是考虑组合索引吧。

    ;with t1 as 
    (	SELECT C3,C6,YEAR1,ID,SUM(DJ1) AS DJ1
    	FROM   A6
    	GROUP  BY C3, C6, YEAR1, ID
    ),t2 as
    (	SELECT C3, C6, YEAR1, SUM(DJ2) AS DJ2
    	FROM   A4
    	GROUP  BY CA3, CA6, YEAR1)
    select t1.*,t1.DJ1/t2.DJ2 as INSTALLRATE from t1 
    join t2
    on t1.C3 = t2.C3 AND t1.C6 = t2.C6 AND t1.YEAR1 = t2.YEAR1 

    2012年7月9日 3:30
    版主