MySQL - 按时间获取最后一条记录（如果为空，则获取第一条）

Question

我有以下 SQL Server 2008 查询：

SELECT T.*,Data.Value FROM [Table] T OUTER APPLY      

(SELECT TOP 1 E.Value FROM [Table2] E     
ORDER BY CASE WHEN T.TDateTime >= E.EDateTime then 1 else 2 end,
ABS(DateDiff(ss,T.TDateTime,E.EDatetime))) AS Data

这基本上是获取T中每条记录的最后一个E值，但如果T中的记录在E的第一条记录之前，则获取E的第一条记录。

MySQL中的等效语句是什么？

编辑

---

下面是我的架构和数据：

DROP TABLE IF EXISTS `data`;
CREATE TABLE `data` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `DataDateTime` datetime DEFAULT NULL,
  `Value` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;


LOCK TABLES `data` WRITE;
INSERT INTO `data` VALUES (1,'2012-02-01 00:00:00',1),(2,'2012-03-01 01:00:00',2),(3,'2012-04-01 02:00:00',3),(4,'2012-05-01 03:00:00',4),(5,'2012-06-01 04:00:00',5),(6,'2012-07-01 05:00:00',6),(7,'2012-08-01 06:00:00',7),(8,'2012-09-01 07:00:00',8);
UNLOCK TABLES;


DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `TDateTime` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

LOCK TABLES `t` WRITE;
INSERT INTO `t` VALUES (1,'2012-01-01 00:00:00'),(2,'2012-02-01 00:00:00'),(3,'2012-02-01 12:00:00'),(4,'2012-03-01 00:00:00'),(5,'2012-04-01 00:00:00'),(6,'2012-05-01 12:00:00'),(7,'2012-06-01 00:00:00'), (8,'2012-07-01 00:00:00');
UNLOCK TABLES;

SQLFiddle:

CREATE TABLE `data` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `DataDateTime` datetime DEFAULT NULL,
  `Value` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
);

INSERT INTO `data` (`DataDateTime`, `Value`) VALUES 
('2012-02-01 00:00:00',1),
('2012-03-01 01:00:00',2),
('2012-04-01 02:00:00',3),
('2012-05-01 03:00:00',4),
('2012-06-01 04:00:00',5),
('2012-07-01 05:00:00',6),
('2012-08-01 06:00:00',7),
('2012-09-01 07:00:00',8);


CREATE TABLE `t` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `TDateTime` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
);

INSERT INTO `t` (`TDateTime`) VALUES 
('2012-01-01 00:00:00'),
('2012-02-01 00:00:00'),
('2012-02-01 12:00:00'),
('2012-03-01 00:00:00'),
('2012-04-01 00:00:00'),
('2012-05-01 12:00:00'),
('2012-06-01 00:00:00'),
('2012-07-01 00:00:00');

My Desired Output:

T.ID, T.TDateTime, Data.DataDateTime, Data.Value
1, 2012-01-01 00:00:00, 2012-02-01 00:00:00, 1
2, 2012-02-01 00:00:00, 2012-02-01 00:00:00, 1
3, 2012-02-01 12:00:00, 2012-02-01 00:00:00, 1
4, 2012-03-01 00:00:00, 2012-02-01 00:00:00, 1
5, 2012-04-01 00:00:00, 2012-03-01 01:00:00, 2
6, 2012-05-01 12:00:00, 2012-05-01 03:00:00, 4
7, 2012-06-01 00:00:00, 2012-05-01 03:00:00, 4
8, 2012-07-01 00:00:00, 2012-06-01 04:00:00, 5

- Simon

---

据我所知，OUTER APPLY/CROSS APPLY不是SQL标准的一部分（如果我错了，请纠正我）。它是M$ SQL服务器特定的功能。这是供应商锁定的典型症状。希望你能找到解决办法。 - Namphibian

是的，我知道！希望还有其他方法。如果可以使用另一种方法（例如JOIN等）来实现这一点，我全听着。 - Simon

你能否在上面包含“T”和“E”的模式，或者在sqlfiddle上分享上述示例的链接？ - kasi

是的，你说得对，Andriy。在T-SQL中修改一些简单的东西在MySQL中会更加困难（但我猜在其他情况下可能会反过来）。 - Simon

Answer 1

这是我的提交 :)

select *, if(Segment1Time<=ifnull(Segment2Time,Segment1Time),
             Segment1Value,
             Segment2Value) Value
from
(
  select *,
    (select DataDateTime from `data` where DataDateTime<=t.TDateTime order by DataDateTime desc limit 1) Segment1Time,
    (select Value from `data` where DataDateTime<=t.TDateTime order by DataDateTime desc limit 1) Segment1Value,
    (select DataDateTime from `data` where DataDateTime> t.TDateTime order by DataDateTime limit 1) Segment2Time,
    (select Value from `data` where DataDateTime> t.TDateTime order by DataDateTime limit 1) Segment2Value
  from `t` t
) X
order by tdatetime;

以下为查询的解析结果。这个查询的好处是，如果在data.datadatetime上创建了索引，那么它就可以线性地进行处理，4个子查询会分别生成单个的"SEK"来排序而不必遍历所有记录。理论上，它应该在data表变得越大时表现得更好。

ID  SELECT_TYPE TABLE   TYPE    POSSIBLE_KEYS   KEY KEY_LEN REF ROWS    EXTRA
1   PRIMARY <derived2>  ALL (null)  (null)  (null)  (null)  8   Using filesort
2   DERIVED t   ALL (null)  (null)  (null)  (null)  8   
6   DEPENDENT SUBQUERY  data    ALL DataDateTime    (null)  (null)  (null)  8   Using where; Using filesort
5   DEPENDENT SUBQUERY  data    index   DataDateTime    DataDateTime    9   (null)  1   Using where; Using index
4   DEPENDENT SUBQUERY  data    ALL DataDateTime    (null)  (null)  (null)  8   Using where; Using filesort
3   DEPENDENT SUBQUERY  data    index   DataDateTime    DataDateTime    9   (null)  1   Using where; Using index

- RichardTheKiwi

---

+1 谢谢，我改了一些东西（> 和 < 符号），但这样做的好处是更快速，并且可以轻松地从 c# 中调用。 - Simon

我显然在读取无名表名称上遇到了困难，特别是t和d。我已经解决了这个问题，感谢您的纠正。 - RichardTheKiwi

Answer 2

花了一些时间才理解需求。最终使用OUTER APPLY分析您的基本查询，并将其改为以下内容：

SELECT t.*, data.*
  FROM t
  JOIN (SELECT t_ID = t.ID, 
        data_ID = ISNULL((SELECT TOP 1 ID FROM data WHERE data.DataDateTime <= t.TDateTime ORDER BY DataDateTime DESC),
                         (SELECT TOP 1 ID FROM data WHERE data.DataDateTime > t.TDateTime ORDER BY DataDateTime ASC))
   FROM t) lnk
    ON lnk.t_ID = t.ID
  JOIN data
    ON data.ID = lnk.data_ID
  ORDER BY t.ID

执行计划显示效率较低，这让我感到有些惊讶。但是，在DataDateTime上添加索引可以显著改变这种情况，这可能对您的MSSQL版本很有用。

无论如何，从这里开始，我在MySQL中创建了以下内容：

SELECT t.*, data.*
  FROM t
  JOIN (SELECT t.ID t_ID, 
           COALESCE((SELECT ID FROM data WHERE data.DataDateTime <= t.TDateTime ORDER BY DataDateTime DESC LIMIT 1),
                           (SELECT ID FROM data WHERE data.DataDateTime > t.TDateTime ORDER BY DataDateTime ASC LIMIT 1)) data_ID
   FROM t) lnk
    ON lnk.t_ID = t.ID
  JOIN data
    ON data.ID = lnk.data_ID
  ORDER BY t.ID

看起来做了它应该做的事情...

- deroby

---

刚刚意识到我错过了我的datediff，但它可以很容易地添加。由于这里两个段之间的依赖关系是通过对整个子查询进行合并来表示的，所以这将更加困难，不是吗？ - RichardTheKiwi

说实话，我仍然不确定我是否“理解”/原始/查询，因此我不确定是否需要额外的DateDiff()。我只是尝试根据描述和预期输出来确定需求。 - deroby

Answer 3

GROUP_CONCAT() 是什么？

这个链接可能会有所帮助：http://www.youdidwhatwithtsql.com/comparing-tsql-cross-apply-mysql-groupconcat/280

- user1101365

---

很遗憾，这对我无法达到我的目标没有帮助。在某些情况下，GROUP_CONCAT可能会起作用，然而... - Simon

Answer 4

你可以对t和data进行交叉连接，然后根据SQL Server查询中的ORDER BY对每个t行的data行进行排名。 DATEDIFF(ss, dt1, dt2)部分可以替换为UNIX_TIMESTAMP(dt2) - UNIX_TIMESTAMP(dt1)。可以使用变量实现排名。以下是我的解决方案：

SELECT
  ID,
  TDateTime,
  DataDateTime,
  Value
FROM (
  SELECT
    ID,
    TDateTime,
    DataDateTime,
    Value,
    @rnk := @rnk * (@lastid = ID) + 1 AS rnk,
    @lastid := ID
  FROM (
    SELECT
      t.ID,
      t.TDateTime,
      data.DataDateTime,
      data.Value
    FROM
      t CROSS JOIN data,
      (SELECT @lastid := 0, @rnk := 0) s
    ORDER BY
      t.ID,
      (t.TDateTime >= data.DataDateTime) DESC,
      ABS(UNIX_TIMESTAMP(t.TDateTime) - UNIX_TIMESTAMP(data.DataDateTime))
  ) s
) s
WHERE
  rnk = 1
;

您可以在 SQL Fiddle 上找到一个可工作的演示 链接。

- Andriy M

---

谢谢，这个解决方案有效，希望它能更快一些，但无论如何都是正确的。 - Simon