简短和简单的问题比长/复杂的问题更引人注意.这不是因为我们不能回答，而是有很多问题，而且志愿者付出的时间很少，很难证明有时间阅读大问题.

Short and simple questions tend to get more attention than long/complex ones. This isn't because we can't answer but with so many questions, and so little volunteer time to give, it is hard to justify the time to read big questions.

但是我认为您的基本要求并不那么复杂.您需要一种方法来检索属于某个时间范围的行，或者如果不在该范围内，则提供与该范围最接近的行.

However I think your basic requirement isn't that complex. You want a way to retrieve rows that fall within a time range OR if not in that range provide the closest rows to that range.

在支持ROW_NUMBER()OVER()的数据库中，这非常容易(并且MySQL 8.x计划支持该功能)，但是在那之前，要模拟row_number()，您可以使用变量和有序子查询.

In databases that support ROW_NUMBER() OVER() this is quite easy (and MySQL 8.x is planned to support this), but until that time to emulate row_number() you can use variables and an ordered subquery.

您可以在 SQL小提琴

MySQL 5.6模式设置:

CREATE TABLE `ponumber` (
  `TimeStr` datetime NOT NULL,
  `Value` int(11) NOT NULL,
  UNIQUE KEY `uk_Times` (`TimeStr`));

INSERT INTO `PONumber`     (`TimeStr`,`Value`) VALUES ('2017-09-28 10:47:55',0);
INSERT INTO `PONumber`     (`TimeStr`,`Value`) VALUES ('2017-09-28 06:26:07',1217911);
INSERT INTO `PONumber`     (`TimeStr`,`Value`) VALUES ('2017-09-28 05:24:18',1217906);

CREATE TABLE `batch_number` (
  `TimeStr` datetime NOT NULL,
  `Value` int(11) NOT NULL,
  UNIQUE KEY `uk_Times` (`TimeStr`));

INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-29 12:46:18',5522);
INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-29 12:25:33',5521);
INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-29 11:44:45',5520);
INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-28 06:26:05',5519);
INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-28 05:22:58',5518);

CREATE TABLE `batchweight` (
  `TimeStr` datetime NOT NULL,
  `Value` int(11) NOT NULL,
  UNIQUE KEY `uk_Times` (`TimeStr`));

INSERT INTO `batchweight`  (`TimeStr`,`Value`) VALUES ('2017-09-29 12:46:19',38985);
INSERT INTO `batchweight`  (`TimeStr`,`Value`) VALUES ('2017-09-28 06:26:07',38985);
INSERT INTO `batchweight`  (`TimeStr`,`Value`) VALUES ('2017-09-28 05:23:03',31002);

查询:

SET @bStartTime  := '2017-09-29 11:10:00'   
SET @bEndTime    := '2017-09-29 12:48:00'

SELECT 
      SrcTable, TimeStr, Value
FROM (
      SELECT
            @row_num :=IF( @prev_value=u.SrcTable, @row_num + 1 ,1) AS RowNumber
          , u.*
          , @prev_value := u.SrcTable
      FROM (

          select 'ponumber' SrcTable , TimeStr, `Value`
          from ponumber
          union all
          select 'batch_number' SrcTable , TimeStr, `Value`
          from batch_number
          union all
          select 'batchweight' SrcTable , TimeStr, `Value`
          from batchweight
          ) u
      CROSS JOIN (SELECT @row_num := 1,  @prev_value :='') vars
      ORDER BY SrcTable, TimeStr DESC
      ) d
WHERE (d.TimeStr between @bStartTime and @bEndTime)
   OR (TimeStr < @bStartTime AND RowNumber = 1)

因此，这是为每个源表的最新行计算一个从1开始的行号".然后，可以根据时间范围或行号(如果不在时间范围内)过滤此派生表.

So, what this does is compute a "RowNumber" which starts at 1 for the most recent row for each source table. Then this derived table is filtered either by the time range, or by the row number if not within the time range.

还请注意，我未使用 UNION，但是使用了UNION ALL.性能上有很大差异，应根据需要学习使用每种方法.如果使用UNION，请不要同时使用select distinct，因为这只是浪费时间.

Also note I have NOT used UNION but instead have used UNION ALL. There is a big difference in performance and should learn to use each according to need. If using UNION don't also use select distinct because you are just wasting effort.

结果 :

Results:

|     SrcTable |              TimeStr | Value |
|--------------|----------------------|-------|
|  batchweight | 2017-09-29T12:46:19Z | 38985 |
| batch_number | 2017-09-29T12:46:18Z |  5522 |
| batch_number | 2017-09-29T12:25:33Z |  5521 |
| batch_number | 2017-09-29T11:44:45Z |  5520 |
|     ponumber | 2017-09-28T10:47:55Z |     0 |