22,207
社区成员
发帖
与我相关
我的任务
分享
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
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别名不需要引用,因为别名是声明在子查询外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 行受影响)