文章/答案/技术大牛

发布

社区首页 >问答首页 >从一个带有时间戳的表中获取最大值，并从另一个表中获得一个时间段的3秒。

问从一个带有时间戳的表中获取最大值，并从另一个表中获得一个时间段的3秒。
EN

Stack Overflow用户

提问于 2019-06-30 21:28:37

回答 1查看 53关注 0票数 0

当EndTime表值为1时，我需要用TimeStr值显示hopper2_setpoint_met，并且需要从TimeStr +3 second的hopper2_liveweight表中显示Weight作为MAX(Value)，当设置点满足的表值为1时，从TimeStr值开始。

示例所期望的结果

+---------------------+--------+
|       EndTime       | Weight | 
+---------------------+--------+
| 2019-06-30 12:02:33 |   300  |
| 2019-06-30 12:04:45 |   299  | 
+---------------------+--------+

数据和我的尝试

您可以从这个SQLFiddle帖子访问这些数据，我在这里构建了模式并插入了一小部分数据，或者如果您想用它在您自己的MySQL数据库表上创建数据，那么这里也提供了相应的SQL。

CREATE TABLE `hopper2_setpoint_met` (
  `TimeStr` datetime NOT NULL,
  `Value` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `hopper2_setpoint_met` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:02:33',1);
INSERT INTO `hopper2_setpoint_met` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:03:51',0);
INSERT INTO `hopper2_setpoint_met` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:45',1);
INSERT INTO `hopper2_setpoint_met` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:05:56',0);
INSERT INTO `hopper2_setpoint_met` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:49',1);

CREATE TABLE `hopper2_liveweight` (
  `TimeStr` datetime NOT NULL,
  `Value` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:02:33',292);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:02:34',298);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:02:35',300);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:02:36',300);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:14',106);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:15',111);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:16',116);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:17',123);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:19',132);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:20',142);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:21',148);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:22',154);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:23',160);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:24',166);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:25',172);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:26',177);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:27',184);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:29',190);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:30',195);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:31',201);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:32',208);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:33',213);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:34',220);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:35',226);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:36',232);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:37',239);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:38',244);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:39',251);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:40',257);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:41',263);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:42',269);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:43',276);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:44',282);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:45',288);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:46',293);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:47',299);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:04:48',299);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:18',108);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:19',112);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:20',117);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:21',123);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:22',128);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:23',135);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:24',141);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:25',148);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:26',154);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:27',160);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:28',165);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:29',172);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:30',177);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:31',183);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:32',190);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:33',196);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:34',201);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:35',208);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:36',213);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:37',220);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:38',226);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:39',231);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:40',237);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:41',242);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:42',248);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:43',254);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:44',262);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:45',269);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:46',276);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:47',282);
INSERT INTO `hopper2_liveweight` (`TimeStr`,`Value`) VALUES ('2019-06-30 12:06:49',288);

昨天，我在MySQL选择打开和关闭时间戳之间的最大值加上3秒上发布了一些类似于传输程序的内容，但我了解到使用临时表并不是最好的解决方案，尽管我在那里得到的答案确实给了我所要求的预期结果。

我还从这些答案中学到了一些东西，并尝试了其中的几个变体，比如If(CAST(Value AS UNSIGNED) != 0, Value, Null) Weight技巧，以及我的新任务中的一些其他东西，而且我也很难获得预期的结果。

如果我将数据放入临时表中，我当然可以得到预期的结果，但它的性能并不好，而且我认为它也使过程复杂化，并不是真正需要的。

我尝试了几个不同的变体，使用JOIN语句和UNION数据以及子查询，当我试图获得timestr+3第二记录之间的最大权重时，没有达到。

我很想学到一些新的东西，在我的脑海中闪现出一个能帮助解决这个问题的答案，因此，这是一个应用类似逻辑而不需要创建临时表或永久临时表的问题。

如果需要的话，使用一些JOIN语句甚至几个子查询将所有这些都保存在一个查询中，这对我来说是可行的。我不确定使用MySQL会话变量是否会有帮助，但我甚至尝试了一些事情，但失败了。

mysql

sql

回答 1

Stack Overflow用户

回答已采纳

发布于 2019-06-30 22:06:12

一个适合的查询是：

SELECT sm.TimeStr AS EndTime, Max(lw.Value) AS Weight
FROM (SELECT TimeStr FROM hopper2_setpoint_met WHERE Value = 1) sm
INNER JOIN hopper2_liveweight lw ON (sm.TimeStr + INTERVAL 3 SECOND) = lw.TimeStr
GROUP BY sm.TimeStr
;

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/56828199

复制

相似问题

问从一个带有时间戳的表中获取最大值，并从另一个表中获得一个时间段的3秒。
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从一个带有时间戳的表中获取最大值，并从另一个表中获得一个时间段的3秒。EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从一个带有时间戳的表中获取最大值，并从另一个表中获得一个时间段的3秒。
EN