我有以下 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