尝试在mysql查询中显示最大时间记录

Question

我目前正在构建一个Mysql数据库，用于处理锂电池充电器的充放电日志，我需要从我的电池单元中显示以下充电信息

`1B     LG HG2 18650    103     00:00:00    0.001   3.042   0   22.1`
`1B     LG HG2 18650    103     00:30:00    0.5     3.442   175.2   23.5`
`1B     LG HG2 18650    103     01:00:00    0.5     3.53    425.4   24.2`
`1B     LG HG2 18650    103     01:30:00    0.5     3.627   675.5   24.7`
`1B     LG HG2 18650    103     02:00:00    0.5     3.683   925.7   24.7`
`1B     LG HG2 18650    103     02:30:00    0.5     3.757   1175.8  25`
`1B     LG HG2 18650    103     03:00:00    0.5     3.851   1426    25.2`
`1B     LG HG2 18650    103     03:30:00    0.5     3.939   1676.1  25`
`1B     LG HG2 18650    103     04:00:00    0.5     4.02    1926.3  25.2`
`1B     LG HG2 18650    103     04:30:00    0.5     4.107   2176.5  24.7`
`1B     LG HG2 18650    103     05:00:00    0.5     4.147   2426.6  24.7`

(间隔30分钟的特定数据，直到5小时标记)现在我想合并充电周期的最后一个日志条目。我在where中添加了另一个or子句，并选择了最大时间

    SELECT 
    `Cell`, 
    `Cell_Make_Model` as `CEll Make And Model`, 
    `Charge_number` as `Charge Number`, 
    `Time_in_charge` as `Charge Clock`, 
    `Current` as `Charge Current`, 
    `Voltage` as `Cell Voltage`, 
    `Capacity` as `Charging Capacity`, 
    `Cell_Temp` as `Cell Temperature (In Celsius)` 
FROM 
    `Cell_Charge_Info` as a 
    INNER JOIN Cell_Make_And_Model b on a.cell = b.Cell_Code 
WHERE 
    b.Cell_Make_Model like "LG HG2 18650" 
    AND time_to_sec(`Time_in_charge`)= 0 
    or b.Cell_Make_Model like "LG HG2 18650" 
    AND time_to_sec(`Time_in_charge`)= 1800 
    or b.Cell_Make_Model like "LG HG2 18650" 
    AND time_to_sec(`Time_in_charge`)= 3600 
    or b.Cell_Make_Model like "LG HG2 18650" 
    AND time_to_sec(`Time_in_charge`)= 5400 
    or b.Cell_Make_Model like "LG HG2 18650" 
    AND time_to_sec(`Time_in_charge`)= 7200 
    or b.Cell_Make_Model like "LG HG2 18650" 
    AND time_to_sec(`Time_in_charge`)= 9000 
    or b.Cell_Make_Model like "LG HG2 18650" 
    AND time_to_sec(`Time_in_charge`)= 10800 
    or b.Cell_Make_Model like "LG HG2 18650" 
    AND time_to_sec(`Time_in_charge`)= 12600 
    or b.Cell_Make_Model like "LG HG2 18650" 
    AND time_to_sec(`Time_in_charge`)= 14400 
    or b.Cell_Make_Model like "LG HG2 18650" 
    AND time_to_sec(`Time_in_charge`)= 16200 
    or b.Cell_Make_Model like "LG HG2 18650" 
    AND time_to_sec(`Time_in_charge`)= 18000 
    or a.Time_in_charge = (
        select 
            max(c.Time_in_charge)
        from 
            Cell_Charge_Info as c 
        where 
            c.cell = a.cell 
            and c.Charge_number = a.Charge_number
            and c.time_in_charge=max(a.time_in_Charge))

当我在phpMyAdmin中执行它时，系统挂起，并且我从mysqld进程获得了100%的CPU利用率。如果我从查询中删除最后一个或-select，那么它是完全有效的，但是结果集不包含费用的最后一个日志条目。任何帮助都将不胜感激。

Answer 1

这不能解决问题--但它肯定更容易阅读……

SELECT Cell
     , Cell_Make_Model  `Cell Make And Model`
     , Charge_number    `Charge Number`
     , Time_in_charge   `Charge Clock`
     , Current          `Charge Current`
     , Voltage          `Cell Voltage`
     , Capacity         `Charging Capacity`
     , Cell_Temp        `Cell Temperature (In Celsius)`
  FROM Cell_Charge_Info a 
  JOIN Cell_Make_And_Model b 
    ON b.Cell_Code = a.cell 
 WHERE (b.Cell_Make_Model = "LG HG2 18650" AND TIME_TO_SEC(Time_in_charge) IN(0,1800,3600,5400,7200,9000,10800,12600,14400,16200,18000)) 
    OR a.Time_in_charge = (SELECT MAX(c.Time_in_charge)
                             FROM Cell_Charge_Info c      
                            WHERE c.cell = a.cell 
                              AND c.Charge_number = a.Charge_number
                              AND c.time_in_charge = MAX(a.time_in_Charge)
                          );

Answer 2

我已经通过一个视图找到了我的问题的解决方案，该视图保存每个费用的最后一个日志和一个联合所有查询。

SELECT 
    `Cell`, 
    `Cell_Make_Model` as `CEll Make And Model`, 
    `Charge_number` as `Charge Number`, 
    `Time_in_charge` as `Charge Clock`, 
    `Current` as `Charge Current`, 
    `Voltage` as `Cell Voltage`, 
    `Capacity` as `Charging Capacity`, 
    `Cell_Temp` as `Cell Temperature (In Celsius)` 
FROM 
    `Cell_Charge_Info` as a 
    INNER JOIN Cell_Make_And_Model b on a.cell = b.Cell_Code 
WHERE 
    b.Cell_Make_Model like "LG HG2 18650" 
    AND time_to_sec(`Time_in_charge`) in (
        0, 1800, 3600, 5400, 7200, 9000, 10800, 
        12600, 14400, 16200, 18000
    ) 
Union ALL 
Select 
    `Cell`, 
    `CEll Make And Model`, 
    `Charge Number`, 
    `Charge Clock`, 
    `Charge Current`, 
    `Cell Voltage`, 
    `Charging Capacity`, 
    `Cell Temperature (In Celsius)` 
from 
    LG_HG2_Charge_Log_MaxTime
order by `Cell`,`Charge Number`,`Charge Clock`

感谢你的指点，让我的代码变得更加整洁！