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

2007-11-29 05:12:24 161 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

用Java实现对接豆包ai火山引擎详细版简单明了ai对话第二部分代码实现。阅读详情

有如下资料
表: 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就没有资料
如何写,请各位指点呀

...全文

STM32F103C8T6最小系统板GPIO配置避坑指南：从原理图到流水灯一次成功本文详细介绍了STM32F103C8T6最小系统板GPIO配置的实战指南，从硬件准备到流水灯实现，涵盖了九大关键步骤。特别针对新手常见问题，如GPIO配置、开发环境搭建和程序下载调试，提供了详细的解决方案和避坑技巧，帮助开发者快速掌握STM32开发基础。阅读详情

mylover002

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

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
这一条怎么解释

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

相关推荐