多表联合条件删除

 

--A，B表满足条件且同时存在的数据删除
delete from table_A  where exists(select 1 from table_B b where table_A.UserId=b.UserId and table_A.IdCard=b.IdCard)

 

--A-B;查询A表中不在B的数据
insert into B (……)
select * from  A where (select count(1) as num from B where A.ID = B.ID) = 0

 

--查询A表（包含多条重复数据）所有用户最接近目标日期的记录
select a.*
 from [DelByLot] a
 inner join 
 (select UserId,max(CreateDate) 'maxgdtime'from [DelByLot] where datediff(day,CreateDate,'2018-4-27')>-1 group by UserId) b 
 on a.UserId=b.UserId and a.CreateDate=b.maxgdtime