SQL优化

Question

  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 )

Answer 1

写错了！

 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 )

Answer 2

单独的语句很难优化的，要知道表结构，数据才好做。

---

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

Answer 3

你好，

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

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.

Answer 4

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

;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