DB2中,如何查询出与某个时间最接近的记录

2022-08-15 社会 196阅读
请参考如下SQL,具体逻辑是:差值的绝对值小到大排序,取第一行即可。
如果需要其他列的值,把取值放where中…… 这样就算有重复值(差值的绝对值相同)也能揪出来~

select T from table(select T, abs($T_STR-T) as diff from A) order by diff fetch first 1 rows only

例子:
db2 => select * from a

T
--------------------------
2014-04-02-01.00.00.000000
2014-05-02-01.00.00.000000
2014-06-02-01.00.00.000000

3 record(s) selected.

db2 => values timestamp('2014-05-03-01.00.00.00000')

1
--------------------------
2014-05-03-01.00.00.000000

1 record(s) selected.

db2 => select T from table(select T, abs('2014-05-03-01.00.00.00000'-T) as diff from A) order by diff fetch first 1 rows only

T
--------------------------
2014-05-02-01.00.00.000000

1 record(s) selected.

db2 =>
声明:你问我答网所有作品(图文、音视频)均由用户自行上传分享,仅供网友学习交流。若您的权利被侵害,请联系fangmu6661024@163.com