--检索上月未拜访客户 select c.* from customer c where not exists(select 1 from bfjl where CUSID=c.CUSID and DATEDIFF(m, bfrq, GETDATE())=1)--检索本月未拜访客户 select c.* from customer c where not exists(select 1 from bfjl where CUSID=c.CUSID and DATEDIFF(m, bfrq, GETDATE())=0)
declare @bs_customer table (cusid int,cusname varchar(1),wfname varchar(1)) insert into @bs_customer select 1,'a','b' union all select 2,'c','b' union all select 3,'d','s'declare @bfjl table (cusid int,bfrq datetime) insert into @bfjl select 1,'2009-07-04' union all select 2,'2008-06-23'--查询出了前一个月内,在表B中有数据的记录 select a.*,b.bfrq from @bs_customer a right join @bfjl b on a.cusid=b.cusid where datediff(m, b.bfrq, getdate())<=1 /* cusid cusname wfname bfrq ----------- ------- ------ ----------------------- 1 a b 2009-07-04 00:00:00.000 */--在上个月以前还有多少家客户没有进行拜访 select a.* from @bs_customer a left join @bfjl b on a.cusid=b.cusid where b.bfrq is null or datediff(m, b.bfrq, getdate())<=1/* cusid cusname wfname ----------- ------- ------ 1 a b 3 d s */--当月还有多少家客户没有拜访select a.* from @bs_customer a left join @bfjl b on a.cusid=b.cusid where datediff(m, b.bfrq, getdate())>1 or b.bfrq is null /* cusid cusname wfname ----------- ------- ------ 2 c b 3 d s */
不在的条件,可以用子查询得到 select * from bs_customer where cusid not in (select cusid from bfjl where year(bfrq)=@year and month(bfrq)=@month) 查哪个月的都行。
select
c.*
from
customer c
where
not exists(select 1 from bfjl where CUSID=c.CUSID and DATEDIFF(m, bfrq, GETDATE())=1)--检索本月未拜访客户
select
c.*
from
customer c
where
not exists(select 1 from bfjl where CUSID=c.CUSID and DATEDIFF(m, bfrq, GETDATE())=0)
declare @bs_customer table (cusid int,cusname varchar(1),wfname varchar(1))
insert into @bs_customer
select 1,'a','b' union all
select 2,'c','b' union all
select 3,'d','s'declare @bfjl table (cusid int,bfrq datetime)
insert into @bfjl
select 1,'2009-07-04' union all
select 2,'2008-06-23'--查询出了前一个月内,在表B中有数据的记录
select a.*,b.bfrq from @bs_customer a right join @bfjl b
on a.cusid=b.cusid
where datediff(m, b.bfrq, getdate())<=1
/*
cusid cusname wfname bfrq
----------- ------- ------ -----------------------
1 a b 2009-07-04 00:00:00.000
*/--在上个月以前还有多少家客户没有进行拜访
select a.* from @bs_customer a left join @bfjl b
on a.cusid=b.cusid
where b.bfrq is null or datediff(m, b.bfrq, getdate())<=1/*
cusid cusname wfname
----------- ------- ------
1 a b
3 d s
*/--当月还有多少家客户没有拜访select a.* from @bs_customer a left join @bfjl b
on a.cusid=b.cusid
where datediff(m, b.bfrq, getdate())>1 or b.bfrq is null
/*
cusid cusname wfname
----------- ------- ------
2 c b
3 d s
*/
select * from bs_customer where cusid not in
(select cusid from bfjl where year(bfrq)=@year and month(bfrq)=@month)
查哪个月的都行。