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

问题描述：

以下是我称为'数据点'的表。我正在尝试检索具有相同'timeOfReading'和'sensorNumber'的'sensorValue'不同实例的实例。检索具有2列匹配和1个不同的行

例如：

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。我也尝试设置绑定两个表1和表2以及但是查询只运行至超时没有显示结果，无论我把什么...

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

如果是的话，我该如何正确限制我的查询来有效地运行搜索？如果没有做错了，这是行不通的？

Select rows having 2 columns equal value

编辑：错误代码：查询600.000秒期间2013年失去的连接到MySQL服务器

当我试图再次运行查询，除非我重新启动错误代码我得到这个错误：2006 。MySQL服务器已经消失0.000秒

技术上我也应该加上：“AND table1.sensorValue！= table2.sensorV Alue“，因为我正在寻找他们不同的实例。 – NutellaAddict

答

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

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);

这一个实际上跑到完成，但结果空。试图找到这些重复的解决方案，我会尽快回复您。运行还需要很长时间。 – NutellaAddict

@NutellaAddict：为了提高sql性能，您需要解释说明并检查问题所在。另外，您需要为条件中使用的列添加索引。 –

@NutellaAddict我用你的示例数据得到一个结果：http://www.sqlfiddle.com/#!9/beeb0/1 – Barmar

答

我想你已经错过了一个条件。也可以添加一个不适用的条件来退出只有不同值的实例。

SELECT * FROM new_table a 
where exists (select * from new_table b 
where a.num=b.num and a.timeRead=b.timeRead and a.value!=b.value);

谢谢，我刚刚意识到这一点，并在评论中添加。但问题仍然是，查询只是没有完成运行，或没有附加条件。 – NutellaAddict

@NutellaAddict不要在评论中添加重要的东西，编辑问题。 – Barmar

@Barmar好点，我补充说。 – NutellaAddict

答

此查询将返回sensorNumber和地方有不同的价值观timeOfReading sensorValue：

select sensorNumber, timeOfReading 
from tablename 
group by sensorNumber, timeOfReading 
having count(distinct sensorValue)>1

个

这将返回实际的记录：

select t.* 
from 
    tablename t inner join (
    select sensorNumber, timeOfReading 
    from tablename 
    group by sensorNumber, timeOfReading 
    having count(distinct sensorValue)>1 
) d on t.sensorNumber=d.sensorNumber and t.timeOfReading=d.timeOfReading

我建议你在sensorNumber添加索引，timeOfReading

alter table tablename add index idx_sensor_time (sensorNumber, timeOfReading)

答

你可以试试这个查询

select testTable.* from testTable inner join (

SELECT sensorNumber,timeOfReading 
FROM testTable 
group by sensorNumber , timeOfReading having Count(distinct sensorValue) > 1) t 
on 
t.sensorNumber = testTable.sensorNumber and t.timeOfReading = testTable.timeOfReading;

这里sqlFiddle

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

相关推荐