请高手指教数据库中两张表如何匹配出时间差最近的记录

ohyeah_16888 2018-02-08 07:15:02
...全文
1591 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
RINK_1 2018-02-09
  • 打赏
  • 举报
回复

SELECT * FROM TABLE_A A
OUTER APPLY (SELECT TOP 1 *,DATEDIFF(MS,A.START_TIME,B.TEST_DATE) AS DATE_DIFF FROM TABLE_B ORDER BY ABS(DATEDIFF(MS,A.START_TIME,TEST_DATE))) AS B
ohyeah_16888 2018-02-09
  • 打赏
  • 举报
回复


这分别是A B 两张表,刚才执行时,报了错。
文盲老顾 2018-02-09
  • 打赏
  • 举报
回复
哦哦,日期相差太大,无法获取毫秒差 加个where限制一下,abs(datediff(d,start_time,test_date))<2限制在两天内
ohyeah_16888 2018-02-09
  • 打赏
  • 举报
回复
谢谢,这个方法本身没问题,但是我的数据量有几十万行,就会报下面的错误了。 Msg 535, Level 16, State 0, Line 1 The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
文盲老顾 2018-02-09
  • 打赏
  • 举报
回复
with tb_a as (
	select convert(datetime,'2018-1-1 0:0:22:022') as start_time,520002050118442 as imsi
	union all
	select '2018-1-1 0:1:35:135',520002053977310
	union all
	select '2018-1-1 0:2:43:243',520002093131289
	union all
	select '2018-1-1 0:4:20:420',520002094861173
),tb_b as (
	select convert(datetime,'2018-1-1 0:0:4:4') as test_date,166959463 as device_id
	union all
	select '2018-1-1 0:0:9:9',190637353
	union all
	select '2018-1-1 0:0:14:14',178689640
	union all
	select '2018-1-1 0:1:34:134',182398775
	union all
	select '2018-1-1 0:0:18:18',182878715
	union all
	select '2018-1-1 0:2:30:230',186146515
	union all
	select '2018-1-1 0:0:40:40',152080013
	union all
	select '2018-1-1 0:4:24:424',176466713
)
select * from (select *,datediff(MILLISECOND,test_date,a.start_time) as sec,row_number() over(partition by a.start_time order by abs(datediff(MILLISECOND,test_date,a.start_time))) as rowid from tb_a a,tb_b b) a where rowid=1
start_time              imsi                                    test_date               device_id   sec         rowid
----------------------- --------------------------------------- ----------------------- ----------- ----------- --------------------
2018-01-01 00:00:22.023 520002050118442                         2018-01-01 00:00:18.017 182878715   4006        1
2018-01-01 00:01:35.137 520002053977310                         2018-01-01 00:01:34.133 182398775   1003        1
2018-01-01 00:02:43.243 520002093131289                         2018-01-01 00:02:30.230 186146515   13013       1
2018-01-01 00:04:20.420 520002094861173                         2018-01-01 00:04:24.423 176466713   -4003       1

(4 行受影响)
换个写法,2楼的代码b别名不需要引用,因为别名是声明在子查询外
文盲老顾 2018-02-09
  • 打赏
  • 举报
回复
用我写的第二个方法可以找到差值最小的(rowid=1),差值第二小的(rowid=2)等等,相对第一个指令需要改动的地方更少
文盲老顾 2018-02-09
  • 打赏
  • 举报
回复
with tb_a as (
	select convert(datetime,'2018-1-1 0:0:22:022') as start_time,520002050118442 as imsi
	union all
	select '2018-1-1 0:1:35:135',520002053977310
	union all
	select '2018-1-1 0:2:43:243',520002093131289
	union all
	select '2018-1-1 0:4:20:420',520002094861173
),tb_b as (
	select convert(datetime,'2018-1-1 0:0:4:4') as test_date,166959463 as device_id
	union all
	select '2018-1-1 0:0:9:9',190637353
	union all
	select '2018-1-1 0:0:14:14',178689640
	union all
	select '2018-1-1 0:1:34:134',182398775
	union all
	select '2018-1-1 0:0:18:18',182878715
	union all
	select '2018-1-1 0:2:30:230',186146515
	union all
	select '2018-1-1 0:0:40:40',152080013
	union all
	select '2018-1-1 0:4:24:424',176466713
)
select * from tb_a a
cross apply(
	select top 1 *,datediff(MILLISECOND,test_date,a.start_time) as sec from tb_b order by abs(datediff(MILLISECOND,a.start_time,test_date))
) b

start_time              imsi                                    test_date               device_id   sec
----------------------- --------------------------------------- ----------------------- ----------- -----------
2018-01-01 00:00:22.023 520002050118442                         2018-01-01 00:00:18.017 182878715   4006
2018-01-01 00:01:35.137 520002053977310                         2018-01-01 00:01:34.133 182398775   1003
2018-01-01 00:02:43.243 520002093131289                         2018-01-01 00:02:30.230 186146515   13013
2018-01-01 00:04:20.420 520002094861173                         2018-01-01 00:04:24.423 176466713   -4003

(4 行受影响)
ohyeah_16888 2018-02-08
  • 打赏
  • 举报
回复
表a 表b 时间基本上都不是完全一致的,所以就需要计算并找出与表a中时间差最小的纪录,认为是匹配的记录。

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧