执行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
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
(
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试下..