我想优化此查询
当我在JOIN条件下使用主ID时,查询以下问题正常
CREATE TABLE ssd
SELECT (
SELECT SUM(litres) FROM `atg_old` as s_atg
WHERE s_atg.id=f_atg.id
) as total FROM `atg_old` as f_atg
但是当我使用另一列时,查询需要太长(30分钟(例如。
CREATE TABLE ssd
SELECT (
SELECT SUM(litres) FROM `atg_old`as s_atg WHERE
s_atg.vehicle_id=f_atg.vehicle_id
) as total FROM `atg_old` as f_atg
这是完整的查询
create table newtable
Select start_date, CAST(odo_meter AS UNSIGNED) current_odo,
CAST(odo_meter AS UNSIGNED) - ifnull((Select odo_meter from customer_transactions_dashboard custtrans where custmain.start_date > custtrans.start_date
and CAST(odo_meter AS UNSIGNED) > 0 and custmain.site_id = custtrans.site_id and
custmain.vehicle_id = custtrans.vehicle_id order by custtrans.start_date asc limit 1),CAST(odo_meter AS UNSIGNED)) KmTraveled,
custmain.total_cost current_cost,
custmain.litres current_litre,
(Select sum(total_cost) from customer_transactions_dashboard custtrans where custmain.start_date > custtrans.start_date
and CAST(odo_meter AS UNSIGNED) > 0 and custmain.site_id = custtrans.site_id and
custmain.vehicle_id = custtrans.vehicle_id) `total_cost`,
(Select sum(litres) from customer_transactions_dashboard custtrans where custmain.start_date > custtrans.start_date
and CAST(odo_meter AS UNSIGNED) > 0 and custmain.site_id = custtrans.site_id and
custmain.vehicle_id = custtrans.vehicle_id) `total_liter`,
custmain.site_id, custmain.vehicle_id
from customer_transactions_dashboard custmain;
请回复
您确定只想将总和(升(存储在单独的表中,每个车辆ID太多次了吗?
您可能需要之类的内容(或使用第二列车辆_ID(
create table ssd
select total from (
select sum(s.litres) total, s.vehicle_id from atg_old s
group by s.vehicle_id ) a