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

wooden954 2010-05-28 09:28:17
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
...全文
87 点赞 收藏 18
写回复
18 条回复
wooden954 2010年05月28日
[Quote=引用 8 楼 xys_777 的回复:]
换用秒

SQL code

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 ……
[/Quote]

正确!结帖!给分!
请xys_777 到
http://topic.csdn.net/u/20100528/09/b9537a4b-9fbc-46f5-a82a-a532c238a8d4.html
作个标记
谢谢!
回复 点赞
wooden954 2010年05月28日
[Quote=引用 5 楼 wujinyuan 的回复:]
select * from taba a inner join tabb b on b.t=(select max(t.t) from tabb t where t.T<a.T)
[/Quote]
结果中
3 1 2010-01-01 09:00:00.000 789 4 1 2010-01-01 08:10:00.000 4
这个记录不正确
回复 点赞
zhangsong841 2010年05月28日
[Quote=引用 13 楼 zhangsong841 的回复:]
引用 8 楼 xys_777 的回复:
换用秒

SQL code

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)) )
……
[/Quote]
向永生哥赔不是了,
小弟自己理解错了,
真不好意思
回复 点赞
wooden954 2010年05月28日
[Quote=引用 3 楼 zhangsong841 的回复:]
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)
[/Quote]
这个结果明显记录条数不对,应该为5条记录
回复 点赞
wooden954 2010年05月28日
声明:我所说的时间最近是无论早晚都算,比如7:00这个时刻,在6:59:00和7:00:01中,显然是7:00:01最近,而在6:59:59和7:01:00中,则是6:59:59最近
回复 点赞
zhangsong841 2010年05月28日
[Quote=引用 8 楼 xys_777 的回复:]
换用秒

SQL code

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 ……
[/Quote]

没记错的话,abs是取绝对值,
永生哥取的是tabb离那个时间点最近的,不管是在时间上与tabA的早或晚,
与楼主的提的问题理解上有偏差?
永生哥看看,咱们一起讨论下
回复 点赞
小_爱 2010年05月28日
--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))
)
回复 点赞
小_爱 2010年05月28日
--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))
)
回复 点赞
Mr_Nice 2010年05月28日
[Quote=引用 6 楼 beirut 的回复:]

SQL code
--> 测试数据:[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','12……
[/Quote]

好思路,学习...
回复 点赞
小_爱 2010年05月28日
--> 测试数据:[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 行受影响)

*/
回复 点赞
永生天地 2010年05月28日
换用秒

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 行)


*/

回复 点赞
永生天地 2010年05月28日
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 行)

*/
回复 点赞
小_爱 2010年05月28日
--> 测试数据:[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 行受影响)


*/
回复 点赞
wujinyuan 2010年05月28日

select * from taba a inner join tabb b on b.t=(select max(t.t) from tabb t where t.T<a.T)
回复 点赞
zhangsong841 2010年05月28日
另外对于这两张表,并不需要自动增长的tabid
回复 点赞
zhangsong841 2010年05月28日
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)
回复 点赞
wooden954 2010年05月28日
忘记加主键关键字了,实际上列TabAID和TabBID为自动增长的主键
回复 点赞
wooden954 2010年05月28日
创建表以及插入测试数据的语句为

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 TabA

Insert 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')

回复 点赞
发动态
发帖子
疑难问题
创建于2007-09-28

9308

社区成员

12.1w+

社区内容

MS-SQL Server 疑难问题
社区公告
暂无公告