SQL查询：这可以在没有嵌套查询的情况下完成吗？

问题描述：

下面的查询工作，但是非常计算昂贵（有> 14k条记录）;这可以更有效地完成（例如，没有嵌套查询）？基本上它与同一个表中的实例相匹配，它们代表开启命令（log1）和第一个关闭命令（log2）。SQL查询：这可以在没有嵌套查询的情况下完成吗？

SELECT log1.`deviceID`, log1.`timestamp` AS `on`, log2.`timestamp` as `off` 
FROM `HC2_log_raw` log1, `HC2_log_raw` log2 
WHERE log1.`newValue` > 0 AND log1.`oldValue` = 0 AND log2.`newValue` = 0 AND log1.`deviceID` = log2.`deviceID` AND 
    log2.`timestamp` = 
    (SELECT MIN(log3.`timestamp`) 
    FROM `HC2_log_raw` log3 
    WHERE log3.`timestamp` > log1.`timestamp` AND log3.`deviceID` = log1.`deviceID` AND log3.`newValue`=0)

谢谢！

14K记录并不是很多。你有没有看过执行计划？您可能只是需要一个索引 –

您是否在连接条件列上使用索引？ –

它不会帮助性能，但是您应该切换到明确的'join'语法，这是自1992年以来的ANSI标准！ – HoneyBadger

答

你可以尝试使用JOIN 与GROUP BY和MIN功能存档您预期的结果

SELECT log1.`deviceid`, 
     log1.`timestamp`  AS `on`, 
     MIN(log2.`timestamp`) AS `off` 
FROM `hc2_log_raw` log1 
     INNER JOIN `hc2_log_raw` log2 
       ON log1.`deviceid` = log2.`deviceid` 
        AND log1.`timestamp` < log2.`timestamp` 
WHERE log1.`newvalue` > 0 
     AND log1.`oldvalue` = 0 
     AND log2.`newvalue` = 0 
GROUP BY log1.`deviceid`, log1.`timestamp`

谢谢！像魅力一样工作，速度大约快5倍:)感谢所有其他评论，我仍然需要创建适当的索引，这会使其更快！ – 3JL

deviceid和timestamp上的索引仅将速度提高20％btw – 3JL

您是否尝试在newvalue和oldvalue字段上创建索引？ –

答

检查了这一点。 afaik，where子句比加入on条件更好。

SELECT log1.`deviceID` 
    ,log1.`timestamp` AS `on` 
    ,log2.`timestamp` AS `off` 
FROM 
`HC2_log_raw` log1 
inner join `HC2_log_raw` log2 
on 
log1.`deviceID` = log2.`deviceID` 
where 
(
    log1.`newValue` > 0 
AND log1.`oldValue` = 0 
AND log2.`newValue` = 0 
AND log2.`timestamp` = (
     SELECT MIN(log3.`timestamp`) 
     FROM `HC2_log_raw` log3 
     WHERE log3.`timestamp` > log1.`timestamp` 
      AND log3.`deviceID` = log1.`deviceID` 
      AND log3.`newValue` = 0 
     ) 
)

SQL查询：这可以在没有嵌套查询的情况下完成吗？

相关推荐