两个表，找时间相近记录联合显示

Question

TabA表
TabAID ID T                          Val
--------------------------------------------------------------------------------
7      1  2010-01-01 07:00:00.000    123
8      1  2010-01-01 08:00:00.000    456
9      1  2010-01-01 09:00:00.000    789
10     2  2010-01-01 10:00:00.000    a
11     2  2010-01-01 11:00:00.000    b
TabB表：
TabAID ID   T                          Val
--------------------------------------------------------------------------------
9      1    2010-01-01 06:59:00.000    1
10     1    2010-01-01 07:01:30.000    2
11     1    2010-01-01 07:52:00.000    3
12     1    2010-01-01 08:10:00.000    4
13     1    2010-01-01 09:00:00.000    5
14     2    2010-01-01 11:00:00.000    x
15     2    2010-01-01 12:10:00.000    y
16     2    2010-01-01 12:00:00.000    z
对于TabA表，当ID=1,T=2010-01-01 07:00:00.000时，需要在B表中找到与此时间最接近的那个时间（2010-01-01 06:59:00.000），找到后连接在一起，对于A表中的每一行都需要这样做，那么最终结果应该是
TabAID ID   T                        Val  TabAID  ID   T                        Val
--------------------------------------------------------------------------------
7      1    2010-01-01 07:00:00.000  123  9       1    2010-01-01 06:59:00.000  1
8      1    2010-01-01 08:00:00.000  456  11      1    2010-01-01 07:52:00.000  3
9      1    2010-01-01 09:00:00.000  789  13      1    2010-01-01 09:00:00.000  5
10     2    2010-01-01 11:00:00.000  a    14      2    2010-01-01 11:00:00.000  x
11     2    2010-01-01 12:00:00.000  b    16      2    2010-01-01 12:00:00.000  z

Answer 1

创建表以及插入测试数据的语句为 Create Table TabA(
  TabAID bigint identity(1,1),
  ID bigint,
  T  DateTime,
  Val varchar(20)
)
Create Table TabB(
  TabBID bigint identity(1,1),
  ID bigint,
  T  DateTime,
  Val varchar(20)
)
Delete from TabAInsert into TabA(ID,T,Val) Values (1,'2010-01-01 07:00:00','123')
Insert into TabA(ID,T,Val) Values (1,'2010-01-01 08:00:00','456')
Insert into TabA(ID,T,Val) Values (1,'2010-01-01 09:00:00','789')
Insert into TabA(ID,T,Val) Values (2,'2010-01-01 10:00:00','a')
Insert into TabA(ID,T,Val) Values (2,'2010-01-01 11:00:00','b')Delete From TabB
Insert into TabB(ID,T,Val) Values (1,'2010-01-01 06:59:00','1')
Insert into TabB(ID,T,Val) Values (1,'2010-01-01 07:01:30','2')
Insert into TabB(ID,T,Val) Values (1,'2010-01-01 07:52:00','3')
Insert into TabB(ID,T,Val) Values (1,'2010-01-01 08:10:00','4')
Insert into TabB(ID,T,Val) Values (1,'2010-01-01 09:00:00','5')
Insert into TabB(ID,T,Val) Values (2,'2010-01-01 11:00:00','x')
Insert into TabB(ID,T,Val) Values (2,'2010-01-01 12:10:00','y')
Insert into TabB(ID,T,Val) Values (2,'2010-01-01 12:00:00','z')

Answer 2

忘记加主键关键字了，实际上列TabAID和TabBID为自动增长的主键

Answer 3

select * from taba a,tabb
where a.id=tabb.id 
and tabb.t=(select max(t) from tabb b where b.id=a.id and b.t<=a.t)

Answer 4

另外对于这两张表，并不需要自动增长的tabid

Answer 5

select * from taba a inner join tabb b on b.t=(select max(t.t) from tabb t where t.T<a.T)

Answer 6

--> 测试数据：[ta]
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([TabAID] int,[ID] int,[T] datetime,[Val] varchar(3))
insert [ta]
select 7,1,'2010-01-01 07:00:00.000','123' union all
select 8,1,'2010-01-01 08:00:00.000','456' union all
select 9,1,'2010-01-01 09:00:00.000','789' union all
select 10,2,'2010-01-01 10:00:00.000','a' union all
select 11,2,'2010-01-01 11:00:00.000','b'--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([TabAID] int,[ID] int,[T] datetime,[Val] varchar(1))
insert [tb]
select 9,1,'2010-01-01 06:59:00.000','1' union all
select 10,1,'2010-01-01 07:01:30.000','2' union all
select 11,1,'2010-01-01 07:52:00.000','3' union all
select 12,1,'2010-01-01 08:10:00.000','4' union all
select 13,1,'2010-01-01 09:00:00.000','5' union all
select 14,2,'2010-01-01 11:00:00.000','x' union all
select 15,2,'2010-01-01 12:10:00.000','y' union all
select 16,2,'2010-01-01 12:00:00.000','z'--------------------------------查询开始------------------------------select * from ta a left join tb b 
on 
a.id=b.id 
where
b.t=(select max(t) from tb where id=a.id and t<=a.t)
/*
TabAID      ID          T                       Val  TabAID      ID          T                       Val
----------- ----------- ----------------------- ---- ----------- ----------- ----------------------- ----
7           1           2010-01-01 07:00:00.000 123  9           1           2010-01-01 06:59:00.000 1
8           1           2010-01-01 08:00:00.000 456  11          1           2010-01-01 07:52:00.000 3
9           1           2010-01-01 09:00:00.000 789  13          1           2010-01-01 09:00:00.000 5
11          2           2010-01-01 11:00:00.000 b    14          2           2010-01-01 11:00:00.000 x(4 行受影响)
*/

Answer 7

select a.*,b.* from tabA a,tabB b where a.id=b.id and not exists(select 1 from tabB m where m.id=a.id and  abs(datediff(mi,m.T,a.T))<abs(datediff(mi,b.T,a.T)) )/*TabAID               ID                   T                                                      Val                  TabBID               ID                   T                                                      Val                  
-------------------- -------------------- ------------------------------------------------------ -------------------- -------------------- -------------------- ------------------------------------------------------ -------------------- 
1                    1                    2010-01-01 07:00:00.000                                123                  1                    1                    2010-01-01 06:59:00.000                                1
1                    1                    2010-01-01 07:00:00.000                                123                  2                    1                    2010-01-01 07:01:30.000                                2
2                    1                    2010-01-01 08:00:00.000                                456                  3                    1                    2010-01-01 07:52:00.000                                3
3                    1                    2010-01-01 09:00:00.000                                789                  5                    1                    2010-01-01 09:00:00.000                                5
4                    2                    2010-01-01 10:00:00.000                                a                    6                    2                    2010-01-01 11:00:00.000                                x
5                    2                    2010-01-01 11:00:00.000                                b                    6                    2                    2010-01-01 11:00:00.000                                x（所影响的行数为 6 行）*/

Answer 8

换用秒select a.*,b.* from tabA a,tabB b where a.id=b.id and not exists(select 1 from tabB m where m.id=a.id and  abs(datediff(ss,m.T,a.T))<abs(datediff(ss,b.T,a.T)) )/*
TabAID               ID                   T                                                      Val                  TabBID               ID                   T                                                      Val                  
-------------------- -------------------- ------------------------------------------------------ -------------------- -------------------- -------------------- ------------------------------------------------------ -------------------- 
1                    1                    2010-01-01 07:00:00.000                                123                  1                    1                    2010-01-01 06:59:00.000                                1
2                    1                    2010-01-01 08:00:00.000                                456                  3                    1                    2010-01-01 07:52:00.000                                3
3                    1                    2010-01-01 09:00:00.000                                789                  5                    1                    2010-01-01 09:00:00.000                                5
4                    2                    2010-01-01 10:00:00.000                                a                    6                    2                    2010-01-01 11:00:00.000                                x
5                    2                    2010-01-01 11:00:00.000                                b                    6                    2                    2010-01-01 11:00:00.000                                x（所影响的行数为 5 行）
*/

Answer 9

--> 测试数据：[ta]
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([TabAID] int,[ID] int,[T] datetime,[Val] varchar(3))
insert [ta]
select 7,1,'2010-01-01 07:00:00.000','123' union all
select 8,1,'2010-01-01 08:00:00.000','456' union all
select 9,1,'2010-01-01 09:00:00.000','789' union all
select 10,2,'2010-01-01 10:00:00.000','a' union all
select 11,2,'2010-01-01 11:00:00.000','b'--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([TabAID] int,[ID] int,[T] datetime,[Val] varchar(1))
insert [tb]
select 9,1,'2010-01-01 06:59:00.000','1' union all
select 10,1,'2010-01-01 07:01:30.000','2' union all
select 11,1,'2010-01-01 07:52:00.000','3' union all
select 12,1,'2010-01-01 08:10:00.000','4' union all
select 13,1,'2010-01-01 09:00:00.000','5' union all
select 14,2,'2010-01-01 11:00:00.000','x' union all
select 15,2,'2010-01-01 12:10:00.000','y' union all
select 16,2,'2010-01-01 12:00:00.000','z'--------------------------------查询开始------------------------------
;with t as
(
select a.[TabAID] as aTabAID ,a.[ID] as aid ,a.[T] as at ,a.[Val] as aVal,
b.[TabAID] as bTabAID ,b.[ID] as bid ,b.[T] as bt ,b.[Val] as bVal,
row=row_number() over(partition by a.t order by abs(datediff(mi,a.t,b.t)))
from ta a left join tb b 
on 
a.id=b.id 
)
select * from t where row=1
/*
aTabAID     aid         at                      aVal bTabAID     bid         bt                      bVal row
----------- ----------- ----------------------- ---- ----------- ----------- ----------------------- ---- --------------------
7           1           2010-01-01 07:00:00.000 123  9           1           2010-01-01 06:59:00.000 1    1
8           1           2010-01-01 08:00:00.000 456  11          1           2010-01-01 07:52:00.000 3    1
9           1           2010-01-01 09:00:00.000 789  13          1           2010-01-01 09:00:00.000 5    1
10          2           2010-01-01 10:00:00.000 a    14          2           2010-01-01 11:00:00.000 x    1
11          2           2010-01-01 11:00:00.000 b    14          2           2010-01-01 11:00:00.000 x    1(5 行受影响)*/

Answer 10

--2000
select * from ta a
left join tb b
on a.id=b.id
and not exists(
select 1 from tb where id=a.id a
nd abs(datediff(ss,t,a.t))<abs(datediff(ss,b.t,a.t))
)

Answer 11

--2000
select * from ta a
left join tb b
on a.id=b.id
and not exists(
    select 1 from tb where id=a.id and abs(datediff(ss,t,a.t))<abs(datediff(ss,b.t,a.t))
    )

Answer 12

没记错的话，abs是取绝对值，
永生哥取的是tabb离那个时间点最近的，不管是在时间上与tabA的早或晚，
与楼主的提的问题理解上有偏差？
永生哥看看，咱们一起讨论下

Answer 13

正确！结帖！给分！
请xys_777 到
http://topic.csdn.net/u/20100528/09/b9537a4b-9fbc-46f5-a82a-a532c238a8d4.html
作个标记
谢谢！