php - 显示选择查询数据，但我想用子查询创建新表选择它很好，但问题是查询运行需要 30 分钟

Question

我想优化这个查询

当我在连接条件中使用主 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;

请回复

Answer 1

您确定只想将 sum(litres) 存储在单独的表中，每个车辆 ID 的存储次数太多了吗？

您可能需要以下内容(或者可能需要第二列 vehicle_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