Question

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

表A的记录中有一个时间字段T，现在有一个时间参数T_STR，怎么根据T查询出A中与T_STR时间最接近的记录

Answer 1

请参考如下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 =>