sql日期連續

① sql查詢日期為連續的數據

select 姓名,曠工日期,count(*)
from
(
select * from table
union all
select 姓名,曠工日期-1 as 曠工日期 from table
union all
select 姓名,曠工日期-2 as 曠工日期 from table
)
group by 姓名,曠工日期
having count(*) >=3

② SQL查詢日期為連續的數據是什麼

/*求連續曠工三天以上的數據*/
declare @t table(name varchar(10), [date] datetime,n int default(1))
insert into @t(name,date) select '張三','2011.09.01'
union select '張三','2011.09.08'
union select '張三','2011.09.09'
union select '張三','2011.09.10'
union select '李四','2011.09.06'
union select '李四','2011.09.09'
union select '李四','2011.09.12'
union select '李四','2011.09.15'
union select '小五','2011.08.06'

select * from @t
--select name,COUNT(*) 次數
-- from @t group by name having(COUNT(*)>3)

declare @nm varchar(10),@d datetime,@n int=1,@lastNm varchar(10)='',@lastD datetime='1900.01.01',@lastN int =1
declare cur cursor for select name ,[date],n from @t order by name,date
open cur
fetch next from cur into @nm,@d,@n
while (@@FETCH_STATUS =0 ) begin
if @lastNm =@nm and @d=@lastD+1 begin
update @t set n = @lastN +1 where name=@nm and [date]=@d
select @lastN = n from @t where name=@nm and [date]=@d
set @lastNm=@nm
set @lastD =@d

end
else begin
set @lastNm = @nm
set @lastD =@d
set @lastN = @n
end
fetch next from cur into @nm,@d,@n
end
close cur
deallocate cur

select * from @t where n>=3

③ 如何用 SQL 產生連續日期

Declare @mindate datetime
Declare @maxdate datetime
set @mindate = '2010-01-01'
set @maxdate = '2010-02-01'
;with temptab(date) as
( select @mindate
union all
select dateadd(d,1,temptab.date) as date
from temptab
where dateadd(d,1,temptab.date)<=@maxdate
)
select * from temptab

④ sql 求連續時間段

根據給定時間為基準以2小時為劃分，得出一天劃分出的時間段
declare @time varchar(5)
set @time='11:13'
select ltrim(a.number)+right(@time,3) as [劃分結果]
from master..spt_values a with(nolock),master..spt_values b with(nolock)
where a.type='P' and b.type='P'
and a.number>=left(@time,1) and b.number<=24
and a.number+1=b.number
試試這個語句

⑤ SQL篩選日期連續的記錄

select empid as 員工編號,workdate as 曠工日期一,dateadd(day,1,workdate) as 曠工日期二 from table t1
where
exists
(select * from table where empid=t1.empid and datediff(day,t1.workdate,workdate)=1)
and not exists
(select * from table where empid=t1.empid and datediff(day,t1.workdate,workdate)=2)
and not exists
(select * from table where empid=t1.empid and datediff(day,t1.workdate,workdate)=-1)
存在大一天的數，但不存在大兩天的數，也不存在小一天的數，就是正確結果了

⑥ SQL查詢日期是連續的個數

這個問題我昨天回答過，參見:
http://..com/question/321372546.html

⑦ 如何用SQL返回兩個日期之間的所有連續日期

在層次查詢中，Oracle引入了一個偽列level,用來表示當前行（節點）對應的level，
它從1開始計數，每多一層level的值就加1。
我們可以據此實現對兩個日期/整數之間所有日期/整數的遍歷。
----------------------------------------------------------
SQL> create table test (begin_date date,end_date date);

Table created

SQL> insert into test values(trunc(sysdate),trunc(sysdate+5));

1 row inserted

SQL> select * from test;

BEGIN_DATE END_DATE
----------- -----------
2010-4-6 2010-4-11

SQL> select begin_date,end_date,begin_date+level -1 as today
2 from test
3 connect by begin_date + level -1 <= end_date;

BEGIN_DATE END_DATE TODAY
----------- ----------- -----------
2010-4-6 2010-4-11 2010-4-6
2010-4-6 2010-4-11 2010-4-7
2010-4-6 2010-4-11 2010-4-8
2010-4-6 2010-4-11 2010-4-9
2010-4-6 2010-4-11 2010-4-10
2010-4-6 2010-4-11 2010-4-11

6 rows selected

------------------------------------------------------------------插入兩條記錄，看該查詢語句是否可行
SQL> insert into test values(trunc(sysdate+4),trunc(sysdate+7));

1 row inserted

SQL> select * from test;

BEGIN_DATE END_DATE
----------- -----------
2010-4-6 2010-4-11
2010-4-10 2010-4-13

SQL> select distinct begin_date+level-1 as today
2 from test
3 connect by begin_date+level-1 <= end_date;

TODAY
-----------
2010-4-7
2010-4-13
2010-4-8
2010-4-11
2010-4-9
2010-4-6
2010-4-10
2010-4-12

8 rows selected
--------------------------------------- 根據最大和最小值得查詢
SQL> delete from test where begin_date = to_date('2010-4-10','yyyy-mm-dd');

1 row deleted

SQL> select * from test;

BEGIN_DATE END_DATE
----------- -----------
2010-4-6 2010-4-11

SQL> SELECT one_date
2 FROM (SELECT start_date + level - 1 one_date
3 FROM (SELECT min(begin_date) start_date, max(end_date) end_date
4 FROM test) test
5 connect BY start_date + level - 1 <= end_date ) all_date,
6 test
7 WHERE one_date BETWEEN begin_date AND end_date;

ONE_DATE
-----------
2010-4-6
2010-4-7
2010-4-8
2010-4-9
2010-4-10
2010-4-11

6 rows selected

⑧ 如何通過SQL生成一個包含連續日期的臨時表

declare @iTemp int;
declare @dTemp datetime;
set @iTemp = 0;
set @dTemp = '2016-01-01' ; --起始日期
while @iTemp < 10000
begin
insert into TableName( ColumnName) values( @dTemp);

set @iTemp = @iTemp + 1;
set @dTemp = dateadd( day,@iTemp, @dTemp);
end

⑨ SQL日期循環

select getdate（）, getdate（）-30*1 ,getdate（）-30*2, getdate（）-30*3 , * from XXX
getdate（）當前日期；
getdate（）-30*1 提前1個月；
getdate（）-30*2 提前1個月；
getdate（）-30*3 提前1個月；
此以30天為一個月。

⑩ SQL查詢日期連續的個數

老兄，想了下你的問題，如下情況是你要怎麼返回，假設一共5筆記錄，日期欄位分別為1，2 ，4，5，6。前兩筆是連續的，後三筆也是連續的，那是不是要返回5，還是最小日期到最大日期中間只要存在間斷就返回0！ 具體實現如下，你可以By 日期排序，然後用便宜函數取出上一筆日期，和當前記錄日期比較，然後再最外層檢索結果為1的欄位SUM就可以了，不過要注意，偏移的第一筆資料！不清楚的可以Hi我