mysql - 检索具有 2 列匹配和 1 列不同的行

Question

下面是我的名为“数据点”的表格。我正在尝试检索具有相同“timeOfReading”和“sensorNumber”的“sensorValue”不同实例的实例。

例如:

 sensorNumber sensorValue timeOfReading

      5            5           6
      5            5           6
      5            6           10   <----same time/sensor diff value!
      5            7           10   <----same time/sensor diff value!

结果应该输出:sensorNumber:5, timeOfReading: 10。

我知道这是一个重复的问题，事实上我有下面提供的链接之一供引用 - 但是没有一个解决方案有效，因为我的查询根本没有结束。

下面是我的 SQL 代码:

SELECT table1.sensorNumber, table1.timeOfReading
FROM datapoints table1
WHERE (SELECT COUNT(*)
        FROM datapoints table2
        WHERE table1.sensorNumber = table2.sensorNumber
        AND table1.timeOfReading = table1.timeOfReading 
        AND table1.sensorValue != table2.sensorValue) > 1
        AND table1.timeOfReading < 20;

请注意，我已将 timeOfReading 的界限设置为低至 20。我也尝试为 table1 和 table 2 设置界限，但无论我输入什么，查询都会一直运行到超时而不显示结果......

数据库包含大约 700mb 的数据，所以我认为我不能在合理的时间内在整个数据库上运行它，我想知道这是否是罪魁祸首？

如果是这样，我怎样才能正确地限制我的查询以有效地运行搜索？如果不是，这是什么地方做错了？

Select rows having 2 columns equal value

编辑:错误代码:2013。在查询 600.000 秒期间丢失与 MySQL 服务器的连接

当我尝试再次运行查询时，除非我重新启动，否则我会收到此错误错误代码:2006。MySQL 服务器已消失 0.000 秒

Answer 1

您可以使用自连接来匹配同一表中的相关行。

SELECT DISTINCT t1.sensorNumber, t1.timeOfReading
FROM datapoints AS t1
JOIN datapoints AS t2 
    ON t1.sensorNumber = t2.sensorNumber
    AND t1.timeOfReading = t2.timeOfReading
    AND t1.sensorValue != t2.sensorValue
WHERE t1.timeOfReading < 20

DEMO

要提高性能，请确保您在 sensorNumber 和 timeOfReading 上有一个复合索引:

CREATE INDEX ix_sn_tr on datapoints (sensorNumber, timeOfReading);