mysql - 需要根据顶行结果设置所有行值

Question

我需要选择顶行 AND l1.activityId = t2.activityId 然后我需要能够将 a.scheduleType 的所有行设置为等于“error” - 而不仅仅是上述为 true 的行。目前，我可以根据 l1.activityId = t2.activityId 的每行结果获取要更改的值，但这对我需要的总体结果没有帮助。

CREATE PROCEDURE reportFreeCoolingTrackerNoErrors (
        IN fromDate varchar (50),
        IN toDate varchar (50),
        IN timeZone varchar (50))

    BEGIN
            DECLARE startDate varchar (50);
            DECLARE endDate varchar (50);
            DECLARE mylogID Int;

             SET startDate = FROM_UNIXTIME(fromDate/1000);
             SET endDate = FROM_UNIXTIME(toDate/1000);

SELECT 
   l1.item31985,l1.item31987,a.scheduleType,
    ((l1.item31985 - l1.item31987)*(time_to_sec(timediff(t2.completed, l1.completed)))) / 3600  AS kwDifference,
    ((l1.item31985 - l1.item31987) *  (substr(l.details, instr(l.details , ':' ) +1))) AS cost,
    (((l1.item31985 - l1.item31987) *  (substr(l.details, instr(l.details , ':' ) +1)))
    *(time_to_sec(timediff(t2.completed, l1.completed)) / 3600)) AS costT,
      time_to_sec(timediff(t2.completed, l1.completed)) / 3600 AS coolingHours,
      time_to_sec(timediff(endDate, startDate)) / 3600 AS totalTimeRange,
    (CONVERT_TZ( (FROM_UNIXTIME(fromDate/1000)),'UTC', timeZone) )AS startingDate, 
    (CONVERT_TZ( (FROM_UNIXTIME(toDate/1000)),'UTC', timeZone) ) AS endingDate,DATABASE() AS databaseName,
     CASE
        when l1.activityId = t2.activityId THEN 1
        ELSE 0
    END AS errorCheck  

  FROM logs l 
      INNER JOIN groups g ON g.groupId = l.groupId
      LEFT JOIN groups g1 ON g.parentId = g1.groupId
      LEFT JOIN groups g2 ON g1.parentId = g2.groupId
      LEFT JOIN groups g3 ON g2.parentId = g3.groupId                
      INNER JOIN activities a ON l.logId = a.logId 
      INNER JOIN log1644 l1 ON a.activityId = l1.activityId 
      INNER JOIN log1644 t2 ON t2.recordId = l1.recordid + 1
      INNER JOIN items i ON l.logId = i.logId AND i.name LIKE '%KW%'
      INNER JOIN users u ON l1.userId = u.userId AND i.name LIKE '%KW%'
     WHERE i.itemID = "31985"  AND  l1.activityId = 1257
      AND l1.started 
        BETWEEN startDate
            AND endDate
     ORDER BY l1.recordId,l1.started;

    END //

DELIMITER ;

Answer 1

要选择顶部行，请仅选择降序数据集中的第一行:

SELECT <fields>
FROM TABLE
ORDER BY <your field> DESC
LIMIT 1 ;

由于您是在存储过程中执行此操作，因此您可以将 a.scheduleType 选择到变量中，然后在 where 子句中使用该变量执行选择。