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我