22,209
社区成员
发帖
与我相关
我的任务
分享
UserF UserC 2013/5/5 0:00:00.000
create table A (
UserID nvarchar(10),
SupID nvarchar(10),
RegisterDate datetime
)
insert into A values ('UserB','UserA','2013-08-05')
insert into A values ('UserC','UserB','2013-07-05')
insert into A values ('UserD','UserB','2013-08-05')
insert into A values ('UserE','UserA','2013-08-05')
insert into A values ('UserF','UserC','2013-05-05')
create table B (UserID nvarchar(10))
insert into B values ('UserA')
insert into B values ('UserB')
insert into B values ('UserC')
SELECT * FROM A where SupID not in(
select SupID from A t,B t1
where SupID=t.SupID and t.SupID=t1.UserID
and datediff(month,RegisterDate,getdate())<3
)
create table A (UserID nvarchar(10),SupID nvarchar(10),RegisterDate datetime)
insert into A values ('UserB','UserA','2013-08-05')
insert into A values ('UserC','UserB','2013-07-05')
insert into A values ('UserD','UserB','2013-08-05')
insert into A values ('UserE','UserA','2013-08-05')
create table B (UserID nvarchar(10))
insert into B values ('UserA')
insert into B values ('UserB')
insert into B values ('UserC')
select b.*
from b
where not exists(select 1 from a
where b.UserID=a.SupID
and A.RegisterDate between DATEADD(month,-3,getdate()) and GETDATE())
--结果
/*
UserID
------------------------
UserC
*/
select b.*
from TableB b
where not exists(select 1 from TableA a
where b.SupID=a.UserID
and A.RegisterDate between DATEADD(month,-3,getdate()) and GETDATE())
改一下,你试试
create table A (UserID nvarchar(10),SupID nvarchar(10),RegisterDate datetime)
insert into A values ('UserB','UserA','2013-05-05')
insert into A values ('UserC','UserB','2013-07-05')
insert into A values ('UserD','UserC','2013-08-05')
insert into A values ('UserE','UserD','2013-06-05')
create table B (UserID nvarchar(10))
insert into B values ('UserA')
insert into B values ('UserB')
insert into B values ('UserC')
insert into B values ('UserD')
select b.UserID
from A a
INNER JOIN B b
ON a.SupID=b.UserID
where a.SupID not in(
select SupID
from A
where RegisterDate>DATEADD(mm,-3,GETDATE()))
/*
UserA
UserD
*/
select b.*
from TableB b
where RegisterDate between DATEADD(month,-3,getdate()) and GETDATE()
and not exists(select 1 from TableA a where b.SupID=a.UserID)