求一条SQL题????^^^^^^^^^^^^^^^^^^^

cefriend 2009-01-14 05:45:05

有一张表

a表

字段
startTime endTime price
2008-09-12 2008-09-14 500
2008-09-15 2008-09-19 400
2008-09-20 2008-09-21 300
2008-09-23 2008-09-28 200
2008-09-28 2008-09-29 100
现在我给出条件 startTime endTime 时间段如何查询出时记录如: 2008-09-13 - 2008-09-25 时间段

...全文

158 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

npkaida 2009-01-15

打赏
举报





create table tb(startTime datetime, endTime datetime,   price int)

insert into tb values('2008-09-12' , '2008-09-14' , 500) 

insert into tb values('2008-09-15' , '2008-09-19' , 400) 

insert into tb values('2008-09-20' , '2008-09-21' , 300) 

insert into tb values('2008-09-23' , '2008-09-28' , 200) 

insert into tb values('2008-09-28' , '2008-09-29' , 100) 

go

--定义起始和结束时间

declare @startTime as datetime

declare @endTime as datetime

set @startTime = '2008-09-13'

set @endTime = '2008-09-25'

--这样最简洁：

select * from tb where not ( @endTime<startTime or @startTime > endTime)



drop table tb



/*

startTime                                              endTime                                                price       

------------------------------------------------------ ------------------------------------------------------ ----------- 

2008-09-12 00:00:00.000                                2008-09-14 00:00:00.000                                500

2008-09-15 00:00:00.000                                2008-09-19 00:00:00.000                                400

2008-09-20 00:00:00.000                                2008-09-21 00:00:00.000                                300

2008-09-23 00:00:00.000                                2008-09-28 00:00:00.000                                200



（所影响的行数为 4 行）



*/

firecc05 2009-01-14

打赏
举报



select * from a 

where startTime between '2008-09-13' and '2008-09-25'

and endtime between '2008-09-13' and '2008-09-25'



startTime                          endTime                          price       

---------------------------------- -------------------------------- ----------- 

2008-09-12 00:00:00.000            2008-09-14 00:00:00.000          500

2008-09-15 00:00:00.000            2008-09-19 00:00:00.000          400

2008-09-20 00:00:00.000            2008-09-21 00:00:00.000          300

2008-09-23 00:00:00.000            2008-09-28 00:00:00.000          200



（所影响的行数为 4 行）

ChinaITOldMan 2009-01-14

打赏
举报

用between and

dawugui 2009-01-14

打赏
举报

--或者.



create table tb(startTime datetime, endTime datetime,   price int)

insert into tb values('2008-09-12' , '2008-09-14' , 500) 

insert into tb values('2008-09-15' , '2008-09-19' , 400) 

insert into tb values('2008-09-20' , '2008-09-21' , 300) 

insert into tb values('2008-09-23' , '2008-09-28' , 200) 

insert into tb values('2008-09-28' , '2008-09-29' , 100) 

go

--定义起始和结束时间

declare @startTime as datetime

declare @endTime as datetime

set @startTime = '2008-09-13'

set @endTime = '2008-09-25'



select * from tb where startTime between @startTime and @endTime or endTime between @startTime and @endTime



drop table tb



/*

startTime                                              endTime                                                price       

------------------------------------------------------ ------------------------------------------------------ ----------- 

2008-09-12 00:00:00.000                                2008-09-14 00:00:00.000                                500

2008-09-15 00:00:00.000                                2008-09-19 00:00:00.000                                400

2008-09-20 00:00:00.000                                2008-09-21 00:00:00.000                                300

2008-09-23 00:00:00.000                                2008-09-28 00:00:00.000                                200



（所影响的行数为 4 行）



*/

HAPPY624728390 2009-01-14

打赏
举报

ljhcy99 2009-01-14

打赏
举报

SELECT * FROM TABLE
WHERE CONVERT(DATETIME,startTime ,101)>= CONVERT(DATETIME,'2008-09-13',101)
AND CONVERT(DATETIME,endTime ,101)<= CONVERT(DATETIME,'2008-09-25',101)

dawugui 2009-01-14

打赏
举报

create table tb(startTime datetime, endTime datetime,   price int)

insert into tb values('2008-09-12' , '2008-09-14' , 500) 

insert into tb values('2008-09-15' , '2008-09-19' , 400) 

insert into tb values('2008-09-20' , '2008-09-21' , 300) 

insert into tb values('2008-09-23' , '2008-09-28' , 200) 

insert into tb values('2008-09-28' , '2008-09-29' , 100) 

go

--使用一个临时表

select top 8000 identity(int,0,1) as id into # from syscolumns a,syscolumns b

--定义起始和结束时间

declare @startTime as datetime

declare @endTime as datetime

set @startTime = '2008-09-13'

set @endTime = '2008-09-25'



select distinct m.* from tb m ,  

(select dateadd(day , t.id , @startTime) id from # t where dateadd(day , t.id , @startTime) <= @endTime) n

where n.id between m.startTime and m.endTime

order by m.price desc



drop table tb,#



/*

startTime                                              endTime                                                price       

------------------------------------------------------ ------------------------------------------------------ ----------- 

2008-09-12 00:00:00.000                                2008-09-14 00:00:00.000                                500

2008-09-15 00:00:00.000                                2008-09-19 00:00:00.000                                400

2008-09-20 00:00:00.000                                2008-09-21 00:00:00.000                                300

2008-09-23 00:00:00.000                                2008-09-28 00:00:00.000                                200



（所影响的行数为 4 行）



*/

mengfeihui 2009-01-14

打赏
举报

startTime between '2008/09/13' and '2008/09/25 or endTime between '2008/09/13' and '2008/09/25

dawugui 2009-01-14

打赏
举报

获取两段日期分为内的重复日期
/*
比如：日期范围A：2008-1-1至2008-1-10 日期范围B: 2008-1-5至2008-1-12

能不能帮我得出两个日期范围的交集：2008-1-5、2008-1-6、2008-1-7、2008-1-8、2008-1-9、2008-1-10

SQL库里面有没有函数能够实现啊？？？循环一个一个的得出来也可以
*/
--2000中的写法



--使用一个临时表

select top 8000 identity(int,0,1) as id into # from syscolumns a,syscolumns b



select id from

(

  select dateadd(day , t.id , '2008-1-1') id from # t where dateadd(day , t.id , '2008-1-1') <= '2008-1-10'

  union all

  select dateadd(day , t.id , '2008-1-5') id from # t where dateadd(day , t.id , '2008-1-5') <= '2008-1-12'

) m

group by id 

having count(*) > 1

order by id



drop table #



/*

id                                                     

------------------------------------------------------ 

2008-01-05 00:00:00.000

2008-01-06 00:00:00.000

2008-01-07 00:00:00.000

2008-01-08 00:00:00.000

2008-01-09 00:00:00.000

2008-01-10 00:00:00.000



（所影响的行数为 6 行）

*/

--2005中的写法



WITH SeqNumber AS

(

    SELECT TOP(360)

        ID=ROW_NUMBER() OVER(ORDER BY o.object_id)-1

    FROM sys.columns AS c

        CROSS JOIN sys.objects AS o

),

RangeDate1 AS

(

    SELECT 

        DATEADD(day,ID,'2008-1-1') AS dt

    FROM SeqNumber

    WHERE DATEADD(day,ID,'2008-1-1')<='2008-1-10'

),

RangeDate2 AS

(

    SELECT 

        DATEADD(day,ID,'2008-1-5') AS dt

    FROM SeqNumber

    WHERE DATEADD(day,ID,'2008-1-5')<='2008-1-12'

)

SELECT

    *

FROM RangeDate1 AS A

    JOIN RangeDate2 AS B

        ON A.dt=B.dt



/*

dt                      dt

----------------------- -----------------------

2008-01-05 00:00:00.000 2008-01-05 00:00:00.000

2008-01-06 00:00:00.000 2008-01-06 00:00:00.000

2008-01-07 00:00:00.000 2008-01-07 00:00:00.000

2008-01-08 00:00:00.000 2008-01-08 00:00:00.000

2008-01-09 00:00:00.000 2008-01-09 00:00:00.000

2008-01-10 00:00:00.000 2008-01-10 00:00:00.000



(6 行受影响)



*/