--try,不知道是否这个意思。 declare @dt1 datetime,--开始时间 @dt2 datetime --结束时间 select @dt1='2005-09-20' select @dt2='2005-09-25' select * from custom a where not exists(select 1 from custom where customname=a.customname and productname=a.productname and thedate between @dt1 and @dt2) and a.thedate < @dt1
因为是在以前发生过的。所以就不使用not between直接<@dt1了。
试下:rivery(river),它的是这个意思!!
select customname,productname from custom where thedate not between '2005-10-10' and '2005-10-18' and thedate<'2005-10-10' 有什么问题吗??
我觉的 not EXISTs 有用,但我查询出来就是不行!!是不是语句有错误啊 select distinct productname,customname from main where not exists (select productname,customname from main where thedate>='2005-05-30' and thedate<='2005-05-30')后面哪个子查询出来的结果是正确的,前面哪个查询也是对的,就是两个差集时不对
--测试表及数据 declare @custom table(customname varchar(50),productname varchar(50),thedate datetime) insert into @custom select '金红叶','氧气','2005-09-20' union select '金红叶','氧气','2005-10-17' union select '金红叶','氮气','2005-09-25 ' --处理语句 declare @dt1 datetime,--开始时间 @dt2 datetime --结束时间 select @dt1='2005-10-10' select @dt2='2005-10-18' select * from @custom a where not exists(select 1 from @custom where customname=a.customname and productname=a.productname and thedate between @dt1 and @dt2) and a.thedate < @dt1--结果 /* 金红叶 氮气 2005-09-25 00:00:00.000 */
--如果需要distinct,可以在外部进行 declare @dt1 datetime,--开始时间 @dt2 datetime --结束时间 select @dt1='2005-10-10' select @dt2='2005-10-18' select distinct customname,productname from (select * from @custom a where not exists(select 1 from @custom where customname=a.customname and productname=a.productname and thedate between @dt1 and @dt2) and a.thedate < @dt1) new
declare @dt1 datetime,--开始时间
@dt2 datetime --结束时间
select @dt1='2005-09-20'
select @dt2='2005-09-25'
select * from custom a
where not exists(select 1 from custom where customname=a.customname and productname=a.productname and thedate between @dt1 and @dt2)
and a.thedate < @dt1
where thedate not between '2005-10-10' and '2005-10-18'
and thedate<'2005-10-10' 有什么问题吗??
select distinct productname,customname from main where not exists (select productname,customname from main where thedate>='2005-05-30' and thedate<='2005-05-30')后面哪个子查询出来的结果是正确的,前面哪个查询也是对的,就是两个差集时不对
declare @custom table(customname varchar(50),productname varchar(50),thedate datetime)
insert into @custom
select '金红叶','氧气','2005-09-20' union
select '金红叶','氧气','2005-10-17' union
select '金红叶','氮气','2005-09-25 '
--处理语句
declare @dt1 datetime,--开始时间
@dt2 datetime --结束时间
select @dt1='2005-10-10'
select @dt2='2005-10-18'
select * from @custom a
where not exists(select 1 from @custom where customname=a.customname and productname=a.productname and thedate between @dt1 and @dt2)
and a.thedate < @dt1--结果
/*
金红叶 氮气 2005-09-25 00:00:00.000
*/
declare @dt1 datetime,--开始时间
@dt2 datetime --结束时间
select @dt1='2005-10-10'
select @dt2='2005-10-18'
select distinct customname,productname
from
(select * from @custom a
where not exists(select 1 from @custom where customname=a.customname and productname=a.productname and thedate between @dt1 and @dt2)
and a.thedate < @dt1) new