mysql - SQL:可以用更好的方法解决吗？

Question

我确信可以这样问:)

所以，

有一个简单的日志表:

CREATE TABLE `log` (
  `id` int(11) NOT NULL,  << AUTOINC
  `action` int(11) NOT NULL DEFAULT '0',
  `source` varchar(20) ,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  `message` varchar(100) 
) ;

日志中充满了传感器数据，但重要的是传感器读取序列每 1 分钟调用一次。因此，每分钟都会添加 2 个新行(2 个传感器)(并不总是如此 - 就像传感器故障一样)。

这里是几分钟前拍摄的实时日志表数据:

id   action source                  time                  message
9468    3   TEMP_28-021501c7b0ff    2015-06-04 23:28:03     24437
9467    3   TEMP_10-000802b59f3f    2015-06-04 23:28:02     24375
9466    3   TEMP_28-021501c7b0ff    2015-06-04 23:27:03     24437
9465    3   TEMP_10-000802b59f3f    2015-06-04 23:27:02     24375
9464    3   TEMP_28-021501c7b0ff    2015-06-04 23:26:03     24437
9463    3   TEMP_10-000802b59f3f    2015-06-04 23:26:02     24375
9462    3   TEMP_28-021501c7b0ff    2015-06-04 23:25:04     24437
9461    3   TEMP_10-000802b59f3f    2015-06-04 23:25:02     24312
9460    3   TEMP_28-021501c7b0ff    2015-06-04 23:24:03     24437
9459    3   TEMP_10-000802b59f3f    2015-06-04 23:24:02     24375

正如您所看到的，传感器每分钟都会被读取一次，并在“消息”列中提供几乎相同的值。

重点是我必须更改表布局，以便它将在一行中包含两 strip 有第一个传感器读取时间时间戳的“消息”。

所以结果应该是这样的:

2015-06-04 23:28:03     24437 24375
2015-06-04 23:27:03     24437 24375
2015-06-04 23:26:03     24437 24375
etc

似乎我已经找到了解决方案，但看起来很糟糕:

SELECT l1.time as l1time,l1.message as l1mess , l2.message as l2mess, 
FROM log l1,log l2 
WHERE l1.source LIKE 'TEMP_10%' AND l2.source LIKE 'TEMP_28%' 
AND l2.id-l1.id=1 AND l1.action=3 AND l2.action=3 
AND ABS(TIMESTAMPDIFF(SECOND,l2.time,l1.time))<10;

当然有更好的解决方案......

Answer 1

您的查询格式更好:

SELECT l1.time as l1time,l1.message as l1mess, l2.message as l2mess, 
FROM log l1 JOIN
     log l2 
     ON l1.source LIKE 'TEMP_10%' AND l2.source LIKE 'TEMP_28%' AND
        l2.id - l1.id = 1 AND l1.action = 3 AND l2.action = 3 ABD
        ABS(TIMESTAMPDIFF(SECOND,l2.time, l1.time)) < 10;

这可能是编写查询的最佳方式。 。 。几乎。为了最好地使用索引(例如 log(action, id))，这个 on 子句更好:

     ON l1.source LIKE 'TEMP_10%' AND l2.source LIKE 'TEMP_28%' AND
        l2.id = l1.id + 1 AND l1.action = 3 AND l2.action = 3 ABD
        ABS(TIMESTAMPDIFF(SECOND, l2.time, l1.time)) < 10;

我不能 100% 确定这确实满足您的真正需要。但您确实声称此查询适合您。