---------------------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-05-05 18:09:22 -- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) -- Blog : http://blog.csdn.net/htl258 ------------------------------------------------------------------------------------> 生成测试数据表: [t1] IF OBJECT_ID('[t1]') IS NOT NULL DROP TABLE [t1] GO CREATE TABLE [t1] ([time] [nvarchar](10),[name] [nvarchar](10),[value] [int]) INSERT INTO [t1] SELECT '11:58:00','dx','123' UNION ALL SELECT '11:59:00','dx','456' UNION ALL SELECT '15:01:00','dx','789' UNION ALL SELECT '22:09:00','dx','123' UNION ALL SELECT '23:00:00','dx','254'--> 生成测试数据表: [t2] IF OBJECT_ID('[t2]') IS NOT NULL DROP TABLE [t2] GO CREATE TABLE [t2] ([time] [nvarchar](10)) INSERT INTO [t2] SELECT '12:00:00' UNION ALL SELECT '13:00:00' UNION ALL SELECT '14:00:00' UNION ALL SELECT '15:00:00' UNION ALL SELECT '16:00:00' UNION ALL SELECT '17:00:00' UNION ALL SELECT '18:00:00' UNION ALL SELECT '19:00:00' UNION ALL SELECT '20:00:00' UNION ALL SELECT '21:00:00' UNION ALL SELECT '22:00:00' UNION ALL SELECT '23:00:00' UNION ALL SELECT '00:00:00'-->SQL查询如下: SELECT A.*,ISNULL(B.value,0) value FROM t2 A OUTER APPLY( SELECT TOP 1 value FROM t1 --假设匹配范围在标准时间的前后30分钟(可以自己调整匹配范围) WHERE A.time BETWEEN DATEADD(mi,-30,time) AND DATEADD(mi,30,time) ORDER BY ABS(DATEDIFF(mi,time,A.time)) ) b /* time value ---------- ----------- 12:00:00 456 13:00:00 0 14:00:00 0 15:00:00 789 16:00:00 0 17:00:00 0 18:00:00 0 19:00:00 0 20:00:00 0 21:00:00 0 22:00:00 123 23:00:00 254 00:00:00 0(13 行受影响) */
-- Author : htl258(Tony)
-- Date : 2010-05-05 18:09:22
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [t1]
IF OBJECT_ID('[t1]') IS NOT NULL
DROP TABLE [t1]
GO
CREATE TABLE [t1] ([time] [nvarchar](10),[name] [nvarchar](10),[value] [int])
INSERT INTO [t1]
SELECT '11:58:00','dx','123' UNION ALL
SELECT '11:59:00','dx','456' UNION ALL
SELECT '15:01:00','dx','789' UNION ALL
SELECT '22:09:00','dx','123' UNION ALL
SELECT '23:00:00','dx','254'--> 生成测试数据表: [t2]
IF OBJECT_ID('[t2]') IS NOT NULL
DROP TABLE [t2]
GO
CREATE TABLE [t2] ([time] [nvarchar](10))
INSERT INTO [t2]
SELECT '12:00:00' UNION ALL
SELECT '13:00:00' UNION ALL
SELECT '14:00:00' UNION ALL
SELECT '15:00:00' UNION ALL
SELECT '16:00:00' UNION ALL
SELECT '17:00:00' UNION ALL
SELECT '18:00:00' UNION ALL
SELECT '19:00:00' UNION ALL
SELECT '20:00:00' UNION ALL
SELECT '21:00:00' UNION ALL
SELECT '22:00:00' UNION ALL
SELECT '23:00:00' UNION ALL
SELECT '00:00:00'-->SQL查询如下:
SELECT A.*,ISNULL(B.value,0) value
FROM t2 A
OUTER APPLY(
SELECT TOP 1 value
FROM t1 --假设匹配范围在标准时间的前后30分钟(可以自己调整匹配范围)
WHERE A.time BETWEEN DATEADD(mi,-30,time) AND DATEADD(mi,30,time)
ORDER BY ABS(DATEDIFF(mi,time,A.time))
) b
/*
time value
---------- -----------
12:00:00 456
13:00:00 0
14:00:00 0
15:00:00 789
16:00:00 0
17:00:00 0
18:00:00 0
19:00:00 0
20:00:00 0
21:00:00 0
22:00:00 123
23:00:00 254
00:00:00 0(13 行受影响)
*/