左联接时间-MYSQL - Time in Left Join - MYSQL

我正在尝试生成一个具有稳定时间线的动态图表，因此我需要在x轴上添加额外的时间（分钟）。我需要两列。第1列（时间）显示了一天中的每一分钟，该表只列出了00:00到23:59的时间列（示例表只有20分钟）。我正试图将其与"日志"表中的结果结合起来。查询将筛选我正在查看的机器"名称"，例如机器"名称"1"。然后从"TimeMins"表中列出此机器何时更改状态以及1440分钟。我希望这有帮助？最终，我有一个动态图表，显示了一台机器一整天的状态变化。"时间"列将设置图形的x轴，给出稳定的时间轴

不幸的是，我无法让它正常加入。也许还有其他方法，或者有人能解决这个问题吗？

结果应该类似于…

time       state
00:00       null (or zero)
00:01       null
00:02        1
00:03       null
00:04       null
00:05        0
00:06       null
00:07        1

等等。一天中每分钟的值，无论"日志"中是否有该分钟的结果。

非常感谢。已经做了两天多了。

这是SQLfiddle：

http://sqlfiddle.com/#！9/5f91a0/2

谢谢。杰米。

您可以使用内部选择。

在下面的例子中，我使用3。

select name, beginning, ending - beginning as seconds from (
  select a.name, a.ts as beginning, (
    select min(b.ts) 
    from log b 
    where b.ts > a.ts and a.name = b.name and b.state = 0
  ) as ending
  from log a 
  where a.state = 1
) c
order by beginning;

第二是为每台机器提供的开始和结束

第一个是包装第二个，它只是计算每次运行的秒数，并通过启动对结果进行排序

当机器在a.ts启动后停止并使用相同名称（匹配同一台机器）状态0时，第三个正在尝试查找下一行

select min(b.ts) 
    from log b 
    where b.ts > a.ts and a.name = b.name and b.state = 0

http://sqlfiddle.com/#！9/5f91a0/14

我并不主张将此作为一种解决方案（即使使用此方法，也肯定有更优化的编写此查询的方法），但只是举例说明。。。

DROP TABLE IF EXISTS `log`;
CREATE TABLE `log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) DEFAULT NULL,
  `ts` datetime NOT NULL,
  `state` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `log` VALUES 
(20,1,'2016-05-16 00:03:02',1),
(21,1,'2016-05-16 00:04:03',0),
(22,2,'2016-05-16 00:04:28',1),
(23,2,'2016-05-16 00:06:45',0),
(25,1,'2016-05-16 00:14:50',1),
(26,2,'2016-05-16 00:15:35',1);
DROP TABLE IF EXISTS `TimeMins`;
CREATE TABLE `TimeMins` (
t time PRIMARY KEY 
);
INSERT INTO `TimeMins` VALUES 
('00:00:00'),
('00:01:00'),
('00:02:00'),
('00:03:00'),
('00:04:00'),
('00:05:00'),
('00:06:00'),
('00:07:00'),
('00:08:00'),
('00:09:00'),
('00:10:00'),
('00:11:00'),
('00:12:00'),
('00:13:00'),
('00:14:00'),
('00:15:00'),
('00:16:00'),
('00:17:00'),
('00:18:00'),
('00:19:00'),
('00:20:00');
SELECT a.*
     , b.id
     , b.name
     , b.state 
  FROM 
     ( SELECT DISTINCT CONCAT(DATE(l.ts),' ',t.t) i 
                  FROM log l
                     , timemins t
     ) a 
  LEFT 
  JOIN log b 
    ON DATE_FORMAT(b.ts,'%Y%m%d%h%i') = DATE_FORMAT(a.i,'%Y%m%d%h%i');
+---------------------+------+------+-------+
| i                   | id   | name | state |
+---------------------+------+------+-------+
| 2016-05-16 00:00:00 | NULL | NULL |  NULL |
| 2016-05-16 00:01:00 | NULL | NULL |  NULL |
| 2016-05-16 00:02:00 | NULL | NULL |  NULL |
| 2016-05-16 00:03:00 |   20 | 1    |     1 |
| 2016-05-16 00:04:00 |   21 | 1    |     0 |
| 2016-05-16 00:04:00 |   22 | 2    |     1 |
| 2016-05-16 00:05:00 | NULL | NULL |  NULL |
| 2016-05-16 00:06:00 |   23 | 2    |     0 |
| 2016-05-16 00:07:00 | NULL | NULL |  NULL |
| 2016-05-16 00:08:00 | NULL | NULL |  NULL |
| 2016-05-16 00:09:00 | NULL | NULL |  NULL |
| 2016-05-16 00:10:00 | NULL | NULL |  NULL |
| 2016-05-16 00:11:00 | NULL | NULL |  NULL |
| 2016-05-16 00:12:00 | NULL | NULL |  NULL |
| 2016-05-16 00:13:00 | NULL | NULL |  NULL |
| 2016-05-16 00:14:00 |   25 | 1    |     1 |
| 2016-05-16 00:15:00 |   26 | 2    |     1 |
| 2016-05-16 00:16:00 | NULL | NULL |  NULL |
| 2016-05-16 00:17:00 | NULL | NULL |  NULL |
| 2016-05-16 00:18:00 | NULL | NULL |  NULL |
| 2016-05-16 00:19:00 | NULL | NULL |  NULL |
| 2016-05-16 00:20:00 | NULL | NULL |  NULL |
+---------------------+------+------+-------+