数据表中有如下记录:
GoodsID StoreID RecDate qty
1 01 2004-06-10 10:22 10
1 01 2004-06-10 11:22 11
1 01 2004-06-10 15:22 11
1 01 2004-06-12 11:22 12
1 01 2004-06-12 15:22 13
2 02 2004-06-10 11:22 11
2 02 2004-06-10 17:22 11
2 02 2004-06-11 17:22 12
3 02 2004-06-10 17:22 11
3 02 2004-06-10 18:22 13如何用SQL语句从以上记录中提取出每个GoodsID在每个StoreID中最靠近某个时间点的qty。比如现在给出的时间点是:2004-06-12 11:30,那么提取出的记录应为:
GoodsID StoreID RecDate qty
1 01 2004-06-12 11:22 12
2 02 2004-06-11 17:22 12
3 02 2004-06-10 18:22 13
GoodsID StoreID RecDate qty
1 01 2004-06-10 10:22 10
1 01 2004-06-10 11:22 11
1 01 2004-06-10 15:22 11
1 01 2004-06-12 11:22 12
1 01 2004-06-12 15:22 13
2 02 2004-06-10 11:22 11
2 02 2004-06-10 17:22 11
2 02 2004-06-11 17:22 12
3 02 2004-06-10 17:22 11
3 02 2004-06-10 18:22 13如何用SQL语句从以上记录中提取出每个GoodsID在每个StoreID中最靠近某个时间点的qty。比如现在给出的时间点是:2004-06-12 11:30,那么提取出的记录应为:
GoodsID StoreID RecDate qty
1 01 2004-06-12 11:22 12
2 02 2004-06-11 17:22 12
3 02 2004-06-10 18:22 13
SQL server的写法:
select a.*
from tablename a join
( select
min(abs(datediff(second,RecDate, '2004-06-12 11:30'))) as m
from tablename
group by GoodsID, StoreID
) b
on a.goodsid=b.goodsid and a.storeid=b.storeid
and abs(datediff(second,a.RecDate, '2004-06-12 11:30'))=b.m
group by GoodsID )AS A
inner join
table1 AS T
on A.goodsId=T.goodsId AND abs(DATEDIFF(second,recdate,'2004-06-12 11:30'))=A.B
group by GoodsID )AS A
inner join
table1 AS T
on A.goodsId=T.goodsId AND abs(DATEDIFF(second,recdate,'2004-06-12 11:30'))=A.B
假设表名为TABLE1,用函数datediff求出日期间的差距,再用函数abs()求出其绝对值,对
goodsid分组,求出最小的(用min函数),最后表连接,找到所有字段