请教一个查询问题,求效率最高的解决方案
本帖最后由 s2160487 于 2013-09-05 09:41:00 编辑 Table A --所有用户表
UserID SupID RegisterDate
————————————————————————
Table B --上级代理表
UserID
————————————————————————
其中Table A.SupID 对应 Table B.UserID,表示Table A的用户是B的下级
需求:查询出3个月没有发展新下级的上级的所有下级用户(- -|||有点绕)
先谢谢了,在线等
[解决办法]
select b.*
from TableB b
where not exists(select 1 from TableA a where b.SupID=a.UserID
and 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 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-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
*/
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
)