导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

各位好,请教一个查询判读的逻辑问题?????

mylover002 2007-11-29 05:12:24
有如下资料
表: Table
字段: KeyID char(1)Primary Key, BDate datetime, EDate datetime
有资料为:
KeyID BDate EDdate
A 2007-10-01 2007-10-05
B 2007-10-10 2007-10-15
C 2007-10-16 2007-10-20
D 2007-10-22 2007-10-28

查询在某个日期范围内是否在表中都存在资料,如果都有这返回1,否则返回0
比如:
1. 查询 2007-10-02, 2007-10-04 在这两个日期范围内就存在有资料,
2. 查询 2007-10-04, 2007-10-15 在这两个日期范围内不是都有资料,比如2007-10-06就没有资料
如何写,请各位指点呀
...全文
60 点赞 收藏 8
写回复
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
mylover002 2007-12-01
谢谢各位的赐教,经过测试,
背着灵魂漫步,写的函数可以满足我的需求
回复
-狙击手- 2007-11-29
create table tb(KeyID varchar(10),BDate datetime,Edate datetime)
insert into tb values('A', '2007-10-01', '2007-10-05')
insert into tb values('B', '2007-10-10', '2007-10-15')
insert into tb values('C', '2007-10-16', '2007-10-20')
insert into tb values('D', '2007-10-22', '2007-10-28' )
go


declare @dt1 as datetime
declare @dt2 as datetime
set @dt1 = '2007-10-16'
set @dt2 = '2007-10-17'

if exists(select 1 from tb where @dt1 between bdate and edate and @dt2 between bdate and edate)
print '1'
else
print '0'

drop table tb
回复
pt1314917 2007-11-29

create table tab(keyid char(1)primary key,bdate datetime,edate datetime)
insert into tab select 'A','2007-10-01','2007-10-05'
insert into tab select 'B','2007-10-10','2007-10-15'
insert into tab select 'C','2007-10-16','2007-10-20'
insert into tab select 'D','2007-10-22','2007-10-28'

alter function IsExists(@bdate datetime,@edate datetime)
returns int
as
begin
declare @t1 table(data datetime)
declare @t2 table(data datetime)
while(datediff(dd,@bdate,@edate)>=0)
begin
insert into @t1 select @bdate
set @bdate=dateadd(dd,1,@bdate)
end
declare @b datetime
declare @e datetime
declare cur cursor for select bdate,edate from tab
open cur
fetch next from cur into @b,@e
while(@@fetch_status=0)
begin
while(datediff(dd,@b,@e)>=0)
begin
insert into @t2 select @b
set @b=dateadd(dd,1,@b)
end
fetch next from cur into @b,@e
end
close cur
deallocate cur
declare @cnt int
select @cnt=count(1) from @t1 a where not exists (select 1 from @t2 where data=a.data)
if(@cnt!=0)
set @cnt=0
else
set @cnt=1
return @cnt
end

select dbo.IsExists('2007-10-02','2007-10-04')--返回1
select dbo.IsExists('2007-10-04','2007-10-15')--返回0
回复
dawugui 2007-11-29
create table tb(KeyID varchar(10),BDate datetime,EDdate datetime)
insert into tb values('A', '2007-10-01', '2007-10-05')
insert into tb values('B', '2007-10-10', '2007-10-15')
insert into tb values('C', '2007-10-16', '2007-10-20')
insert into tb values('D', '2007-10-22', '2007-10-28' )
go

SELECT TOP 1000 id = identity(int,0,1) INTO tmp FROM syscolumns a, syscolumns b

declare @dt1 as datetime
declare @dt2 as datetime
set @dt1 = '2007-10-02'
set @dt2 = '2007-10-04'

select a.* , 返回 = case when m.keyid is not null then 1 else 0 end from tb a
left join
(select distinct KeyID from(select KeyID , dt = dateadd(day , tmp.id , BDate) from tb ,tmp where dateadd(day , tmp.id , BDate) <= EDdate) t where dt >= @dt1 and dt <= @dt2) m
on a.keyid = m.keyid
/*
KeyID BDate EDdate 返回
---------- ------------------------------------------------------ ------------------------------------------------------ -----------
A 2007-10-01 00:00:00.000 2007-10-05 00:00:00.000 1
B 2007-10-10 00:00:00.000 2007-10-15 00:00:00.000 0
C 2007-10-16 00:00:00.000 2007-10-20 00:00:00.000 0
D 2007-10-22 00:00:00.000 2007-10-28 00:00:00.000 0

(所影响的行数为 4 行)
*/

set @dt1 = '2007-10-04'
set @dt2 = '2007-10-15'
select a.* , 返回 = case when m.keyid is not null then 1 else 0 end from tb a
left join
(select distinct KeyID from(select KeyID , dt = dateadd(day , tmp.id , BDate) from tb ,tmp where dateadd(day , tmp.id , BDate) <= EDdate) t where dt >= @dt1 and dt <= @dt2) m
on a.keyid = m.keyid
/*
KeyID BDate EDdate 返回
---------- ------------------------------------------------------ ------------------------------------------------------ -----------
A 2007-10-01 00:00:00.000 2007-10-05 00:00:00.000 1
B 2007-10-10 00:00:00.000 2007-10-15 00:00:00.000 1
C 2007-10-16 00:00:00.000 2007-10-20 00:00:00.000 0
D 2007-10-22 00:00:00.000 2007-10-28 00:00:00.000 0

(所影响的行数为 4 行)
*/


drop table tb,tmp
回复
dawugui 2007-11-29
create table tb(KeyID varchar(10),BDate datetime,EDdate datetime)
insert into tb values('A', '2007-10-01', '2007-10-05')
insert into tb values('B', '2007-10-10', '2007-10-15')
insert into tb values('C', '2007-10-16', '2007-10-20')
insert into tb values('D', '2007-10-22', '2007-10-28' )
go

SELECT TOP 1000 id = identity(int,0,1) INTO tmp FROM syscolumns a, syscolumns b

declare @dt1 as datetime
declare @dt2 as datetime
set @dt1 = '2007-10-02'
set @dt2 = '2007-10-04'

select a.* , m.返回 from tb a left join
(select distinct KeyID , 返回 = 1 from(select KeyID , dt = dateadd(day , tmp.id , BDate) from tb ,tmp where dateadd(day , tmp.id , BDate) <= EDdate) t where dt >= @dt1 and dt <= @dt2) m
on a.keyid = m.keyid
/*
KeyID BDate EDdate 返回
---------- ------------------------------------------------------ ------------------------------------------------------ -----------
A 2007-10-01 00:00:00.000 2007-10-05 00:00:00.000 1
B 2007-10-10 00:00:00.000 2007-10-15 00:00:00.000 NULL
C 2007-10-16 00:00:00.000 2007-10-20 00:00:00.000 NULL
D 2007-10-22 00:00:00.000 2007-10-28 00:00:00.000 NULL

(所影响的行数为 4 行)

*/

set @dt1 = '2007-10-04'
set @dt2 = '2007-10-15'
select a.* , m.返回 from tb a left join
(select distinct KeyID , 返回 = 1 from(select KeyID , dt = dateadd(day , tmp.id , BDate) from tb ,tmp where dateadd(day , tmp.id , BDate) <= EDdate) t where dt >= @dt1 and dt <= @dt2) m
on a.keyid = m.keyid

/*
KeyID BDate EDdate 返回
---------- ------------------------------------------------------ ------------------------------------------------------ -----------
A 2007-10-01 00:00:00.000 2007-10-05 00:00:00.000 1
B 2007-10-10 00:00:00.000 2007-10-15 00:00:00.000 1
C 2007-10-16 00:00:00.000 2007-10-20 00:00:00.000 NULL
D 2007-10-22 00:00:00.000 2007-10-28 00:00:00.000 NULL

(所影响的行数为 4 行)
*/


drop table tb,tmp
回复
-狙击手- 2007-11-29
if exists(select 1 from table where @s >= bdate and @e <= edate)
print '1'
else
print '0'



这个意思?
回复
kk19840210 2007-11-29
select keyid,bdate,eddate,case when '2007-10-02'>=bdate and '2007-10-04'<=eddate then 1 else 0 end from table
回复
-狙击手- 2007-11-29
比如2007-10-06就没有资料

---
C 2007-10-16 2007-10-20
这一条怎么解释

回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告