22,210
社区成员
发帖
与我相关
我的任务
分享
WITH Tab1(ID,EnName,nDateTime)AS(
SELECT 1,'A','2015-07-28 06:52:22.000' UNION ALL
SELECT 1,'D','2015-07-28 06:53:30.000' UNION ALL
SELECT 2,'D','2015-07-28 06:52:22.000'
)
,Tab2(ID,CnName,nDateTime)AS(
SELECT 1,'东京','2015-07-28 06:53:12.000' UNION ALL
SELECT 1,'北京','2015-07-28 06:53:42.000' UNION ALL
SELECT 2,'北京','2015-07-28 06:53:00.000'
)
SELECT
a.ID,c.EnName,a.CnName,a.nDateTime,c.nDateTime
FROM
Tab2 a
OUTER APPLY( SELECT TOP 1 b.ID,b.EnName,b.nDateTime FROM Tab1 b WHERE b.ID = a.ID ORDER BY ABS(DATEDIFF(MICROSECOND,b.nDateTime,a.nDateTime))) c
SELECT t2.ID, t1.名字, t2.地址, t2.时间
FROM 表2 t2
CROSS APPLY (
SELECT TOP 1 名字
FROM 表1
WHERE ID = t2.ID
AND 时间<= t2.时间
ORDER BY 时间 DESC
) t1
ID 名字 地址 时间
----------- ---- ---- -----------------------
1 A 东京 2015-07-28 06:53:12.000
1 D 北京 2015-07-28 06:53:42.000
2 D 北京 2015-07-28 06:53:00.000
/* 测试数据
WITH 表1(ID,名字,时间)AS(
SELECT 1,'A','2015-07-28 06:52:22.000' UNION ALL
SELECT 1,'D','2015-07-28 06:53:30.000' UNION ALL
SELECT 2,'D','2015-07-28 06:52:22.000'
)
,表2(ID,地址,时间)AS(
SELECT 1,'东京','2015-07-28 06:53:12.000' UNION ALL
SELECT 1,'北京','2015-07-28 06:53:42.000' UNION ALL
SELECT 2,'北京','2015-07-28 06:53:00.000'
) */
SELECT t2.ID, t1.名字, t2.地址, t2.时间
FROM 表2 t2
CROSS APPLY (
SELECT TOP 1 名字
FROM 表1
WHERE 时间<= t2.时间
ORDER BY 时间 DESC
) t1
ID 名字 地址 时间
----------- ---- ---- -----------------------
1 A 东京 2015-07-28 06:53:12.000
1 D 北京 2015-07-28 06:53:42.000
2 A 北京 2015-07-28 06:53:00.000