常见于日期、数值的查询，例如查询离某个给定日期最接近的数据。 ### 创建测试表 ``` create table test_around ( id int auto_increment primary key, data_time datetime null, data_name varchar(50) null ); ``` 填充一些测试数据。 ``` INSERT INTO test_around(data_time, data_name) VALUES ('2023-05-10 08:00:00','1'), ('2023-05-10 20:00:00','2'), ('2023-05-11 08:00:00','3'), ('2023-05-11 20:00:00','4'), ('2023-05-12 08:00:00','5'), ('2023-05-12 20:00:00','6'), ('2023-05-13 08:00:00','7'), ('2023-05-13 20:00:00','8'); ``` ### 测试查询 ``` SELECT * FROM test_around ORDER BY ABS(STR_TO_DATE('2023-05-11 13:00:00', '%Y-%m-%d %H:%i:%S') - data_time) LIMIT 1; ``` +----+---------------------+-----------+ | id | data_time | data_name | +----+---------------------+-----------+ | 9 | 2023-05-11 08:00:00 | 3 | +----+---------------------+-----------+ 1 row in set, 16 warnings (0.01 sec) Loading... 常见于日期、数值的查询，例如查询离某个给定日期最接近的数据。 ### 创建测试表 ``` create table test_around ( id int auto_increment primary key, data_time datetime null, data_name varchar(50) null ); ``` 填充一些测试数据。 ``` INSERT INTO test_around(data_time, data_name) VALUES ('2023-05-10 08:00:00','1'), ('2023-05-10 20:00:00','2'), ('2023-05-11 08:00:00','3'), ('2023-05-11 20:00:00','4'), ('2023-05-12 08:00:00','5'), ('2023-05-12 20:00:00','6'), ('2023-05-13 08:00:00','7'), ('2023-05-13 20:00:00','8'); ``` ### 测试查询 ``` SELECT * FROM test_around ORDER BY ABS(STR_TO_DATE('2023-05-11 13:00:00', '%Y-%m-%d %H:%i:%S') - data_time) LIMIT 1; ``` +----+---------------------+-----------+ | id | data_time | data_name | +----+---------------------+-----------+ | 9 | 2023-05-11 08:00:00 | 3 | +----+---------------------+-----------+ 1 row in set, 16 warnings (0.01 sec) 最后修改：2023 年 08 月 02 日 © 允许规范转载 打赏 赞赏作者 支付宝微信 赞 3 如果觉得我的文章对你有用，请随意赞赏
