mysql - 我对同一个表有两个查询，这些表具有我要加入的不同分组

Question

我对同一个表有两个查询，这些查询具有我已连接在一起的不同分组。该代码可以工作，但看起来很慢。我确信，由于查询的相似性质，可以优化此代码。

SELECT A.BatchID,
       A.Lot_ID,
       A.SN,
       A.StepNum,
       A.StepName,
       A.ProcedureName,
       A.HMI_user,
       A.RecipeName,
       A.batchIdSN,
       A.t_stamp,
       A.Batch_Time AS 'Batch Time',
       B.BatchTimeTotal,
       B.t_stamp_start
FROM (SELECT g1.BatchID,
             g1.Lot_ID,
             g1.SN,
             g1.StepNum,
             g1.StepName,
             g1.ProcedureName,
             g1.HMI_user,
             g1.RecipeName,
             CONCAT(g1.BatchID,g1.SN) AS 'batchIdSN',
             CONVERT_TZ(g1.t_stamp, 'UTC', $timeZone$) AS 't_stamp',
             SUM(TIME_TO_SEC(TIMEDIFF(g2.t_stamp,g1.t_stamp))/60) AS 'Batch_Time'
      FROM `SN_all_Batch_Data` g1
      Left JOIN `SN_all_Batch_Data` g2 ON g2.id = g1.id + 1 and g2.SN = g1.SN
      WHERE g1.BatchID > 0
      GROUP BY g1.BatchID, g1.StepNum) A
LEFT JOIN (SELECT q.BatchID,
                  q.BatchTimeTotal,
                  MIN(q.t_stamp) AS 't_stamp_start'
           FROM(SELECT g1.BatchID,
                       CONVERT_TZ(g1.t_stamp, 'UTC', $timeZone$) AS 't_stamp',
                       SUM(TIME_TO_SEC(TIMEDIFF(g2.t_stamp,g1.t_stamp))/60) AS 'BatchTimeTotal'
                FROM `SN_all_Batch_Data` g1
                Left JOIN `SN_all_Batch_Data` g2 ON g2.id = g1.id + 1 and g2.SN = g1.SN
                WHERE g1.BatchID > 0
                GROUP BY g1.BatchID) q
           GROUP BY q.BatchID) B
ON A.BatchID = B.BatchID

任何优化指导将不胜感激。

Answer 1

假设您提供的查询在您的系统中有效。也许中间表可以省去重复的工作。您可以先创建一个这样的表。

CREATE TABLE temp1 AS
SELECT g1.BatchID,
       g1.Lot_ID,
       g1.SN,
       g1.StepNum,
       g1.StepName,
       g1.ProcedureName,
       g1.HMI_user,
       g1.RecipeName,
       CONCAT(g1.BatchID,g1.SN) AS 'batchIdSN',
       CONVERT_TZ(g1.t_stamp, 'UTC', $timeZone$) AS 't_stamp',
       SUM(TIME_TO_SEC(TIMEDIFF(g2.t_stamp,g1.t_stamp))/60) AS 'BatchTimeTotal'
 FROM `SN_all_Batch_Data` g1
 Left JOIN `SN_all_Batch_Data` g2 ON g2.id = g1.id + 1 and g2.SN = g1.SN
 WHERE g1.BatchID > 0;

那么以下步骤是

SELECT A.*,
       B.BatchTimeTotal,
       B.t_stamp_start
FROM (SELECT g1.BatchID,
             g1.Lot_ID,
             g1.SN,
             g1.StepNum,
             g1.StepName,
             g1.ProcedureName,
             g1.HMI_user,
             g1.RecipeName,
             g1.batchIdSN,
             g1.t_stamp,
             g1.BatchTimeTotal AS 'Batch Time'
      FROM temp1 g1
      GROUP BY g1.BatchID, g1.StepNum) A
LEFT JOIN (SELECT q.BatchID,
                  q.BatchTimeTotal,
                  MIN(q.t_stamp) AS 't_stamp_start'
           FROM temp1 q
           GROUP BY q.BatchID) B
ON A.BatchID = B.BatchID