22,300
社区成员




--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))
)
--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))
)
--> 测试数据:[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 行受影响)
*/
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 行)
*/
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 行)
*/
--> 测试数据:[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 行受影响)
*/
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')