表 T1name danwei
感康 盒
维C 盒表 liushui
id name danwei riqi
1 感康 盒 2010-01-01
2 感康 盒 2012-02-15
3 维C 盒 2012-02-18如何查询出liushui表 2010-01-01 到 2012-02-15 期间段内未包含T1表的其它产品数据,结果为name danwei
维C 盒
感康 盒
维C 盒表 liushui
id name danwei riqi
1 感康 盒 2010-01-01
2 感康 盒 2012-02-15
3 维C 盒 2012-02-18如何查询出liushui表 2010-01-01 到 2012-02-15 期间段内未包含T1表的其它产品数据,结果为name danwei
维C 盒
select b.*
from liushui a join t1 b on a.name = b.name and a.danwei = b.danwei
where a.riqi between '2010-01-01' and '2012-02-15'
--一楼有问题select a.*
from t1 a left join liushui b
on a.name = b.name and a.danwei = b.danwei
and a.riqi between '2010-01-01' and '2012-02-15'
where b.id is null
select a.*
from t1 a left join liushui b
on a.name = b.name and a.danwei = b.danwei
and b.riqi between '2010-01-01' and '2012-02-15'
where b.id is null
2楼也有问题,这个这个
where not exists (select 1 from liushui t2
where t2.name = t1.name and t2.riqi between '2010-01-01' and '2012-02-15')
select '维C', '盒'
declare @t2 table(id int , name varchar(10), danwei varchar(20), riqi datetime)insert into @t2select 1, '感康','盒','2010-01-01' union all
select 2,'感康', '盒', '2012-02-15' union all
select 3, '维C','盒','2012-02-18'select distinct b.* from @t2 a,@t b
where a.riqi between '2010-01-01' and '2012-02-15'
and a.name != b.name
SELECT distinct a.name,a.danwei FROM tt1 a , tt2 b
WHERE a.name NOT IN (b.name) and b.riqi between '2010-01-01' and '2012-02-15'