如何优化SQL语句

[zhengshouquan]

执行t1和t5里的子查询语句1秒钟也不到。但连接起来查询，起码要6秒钟。请教如何优化下列语句.



SELECT t1.*,t5.*
FROM
(
SELECT a.mould_id,b.key_id,
SUM(ROUND(CONVERT(DECIMAL(18,16),c.number_id) * a.weight * a.mat_length * a.mat_width * a.mat_height * a.mat_price,2)) AS amount
FROM price_mat a INNER JOIN price_work b ON a.mould_id = b.mould_id AND a.key_id = b.key_id
INNER JOIN price_nummain c ON b.number_lsh = c.number_lsh
GROUP BY a.mould_id,b.key_id

)t1 INNER JOIN
(
SELECT mould_id,key_id,rate,work_id,b.codename AS work_name,a.try_ton,
SUM(other_amount) AS other_amount,
SUM(total_amount) AS total_amount,
SUM(loc_total_amount) AS loc_total_amount
FROM price_work a INNER JOIN syscode b ON a.work_id = b.codeno
WHERE codetype = 'price_project'
GROUP BY mould_id,key_id,rate,work_id,b.codename,a.try_ton

)t5 ON t1.mould_id = t5.mould_id AND t1.key_id = t5.key_id

[zhengshouquan]

表本来已经建了索引。最后把t1和t2做了两个视图，并在视图上加了索引，也没法解决问题。

[AcHerat]

with t1 as
(
select ...
),t5 as
(
select ...
)

select *
from t1 join t5 on .....
where ....

[叶子]

;with t1 as
(
SELECT a.mould_id,b.key_id,
SUM(ROUND(CONVERT(DECIMAL(18,16),c.number_id)*a.weight*a.mat_length*a.mat_width*a.mat_height*a.mat_price,2)) 
AS amount
FROM price_mat a INNER JOIN price_work b ON a.mould_id = b.mould_id AND a.key_id = b.key_id
INNER JOIN price_nummain c ON b.number_lsh = c.number_lsh
GROUP BY a.mould_id,b.key_id
),t5 as
(
SELECT mould_id,key_id,rate,work_id,b.codename AS work_name,a.try_ton,
SUM(other_amount) AS other_amount,
SUM(total_amount) AS total_amount,
SUM(loc_total_amount) AS loc_total_amount
FROM price_work a INNER JOIN syscode b ON a.work_id = b.codeno  
WHERE codetype = 'price_project'
GROUP BY mould_id,key_id,rate,work_id,b.codename,a.try_ton
)

SELECT t1.*,t5.* FROM t1 INNER JOIN t5 
ON t1.mould_id = t5.mould_id AND t1.key_id = t5.key_id

试下..

[--小F--]

这没什么好优化的了 直接加索引好了

[sysuleiw]

通过实际使用发现，个人觉得inner join效率好差，我之前一个同事写的一个视图用inner join 需要18秒才能查出，我简单修改了下1秒可以搞定，cte也可，其他查询也可，楼主可以尝试下。。

[sysuleiw]

[Quote=引用 3 楼 fredrickhu 的回复:]
这没什么好优化的了 直接加索引好了
[/Quote]

个人觉得不是索引的问题，单独查询效率比较高，说明索引应该没问题，但是一旦关联起来效率就比较低，还是代码逻辑上不够合理。