MySQL – 按时间获取最后一个条目(或者如果为空则首先获取)
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL – 按时间获取最后一个条目(或者如果为空则首先获取)，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4017字，纯文字阅读大概需要6分钟。
内容图文
我有以下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中每条记录的Last 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');
我想要的输出：
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
解决方法:
这是我的提交:)
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;
这是查询的EXPLAIN.这个查询的好处是,对于data.datadatetime的索引,它与4个子查询呈线性关系,所有子查询都生成单个SEEK,而不必通过所有记录对它们进行排名.理论上,数据表变得越大,它应该工作得越好.
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
内容总结
以上是互联网集市为您收集整理的MySQL – 按时间获取最后一个条目(或者如果为空则首先获取)全部内容，希望文章能够帮你解决MySQL – 按时间获取最后一个条目(或者如果为空则首先获取)所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】