① sql查询日期连续的个数
老兄，想了下你的问题，如下情况是你要怎么返回，假设一共5笔记录，日期字段分别为1，2 ，4，5，6。前两笔是连续的，后三笔也是连续的，那是不是要返回5，还是最小日期到最大日期中间只要存在间断就返回0！ 具体实现如下，你可以By 日期排序，然后用便宜函数取出上一笔日期，和当前记录日期比较，然后再最外层检索结果为1的字段SUM就可以了，不过要注意，偏移的第一笔资料！不清楚的可以Hi我
② postgres如何获得一段连续的日期，比如从2015-01-01到2015-01-31之间的每一天，最好一条sql就能搞定。
比较通用的方法
WITHtAS
(
SELECTconvert(datetime,'2015-05-0100:00:00',120)AStdate
UNIONALL
SELECTDATEADD(DAY,1,tdate)
FROMt
WHEREtdate<convert(datetime,'2015-05-3100:00:00',120)
)
SELECT*FROMtOPTION(MAXRECURSION0)
如果日期比较少，也可以用这个方法
selectDATEADD(DAY,number,convert(datetime,'2015-05-0100:00:00',120))
frommaster..spt_valueswheretype='p'
ANDnumber<=DATEDIFF(DAY,'2015-05-0100:00:00','2015-05-3100:00:00')
③ 如何用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
④ sqlserver中如何查询出连续日期记录的代码
你这个需要借助一个流水表，然后通过指定日期对流水表，进行数据叠加操作，
类似流水日期生成方法
---生成4月份所有的天数
selectdateadd(day,number,'2018-4-1')asdfrommaster..spt_values
wheretype='p'
andnumber>=0
anddateadd(day,number,'2018-4-1')between'2018-4-1'anddateadd(day,-1,'2018-5-1')
请试一试，如有疑问，及时沟通！
⑤ SQL数据库 怎么判断年份是否连续
介绍个办法里面相对简单的，暂时不考虑性能。
思路：要是三年连续 则某一年与其他年之差肯定同时包含1，2。.
select
a.id,
a.year,
count(distinct case when a.year-b.year=1 then year end) as diff_1_count,
count(distinct case when a.year-b.year=2 then year end) as diff_2_count
from emp a
left join emp b on a.id=b.id and a.year>b.year
group by a.id,a.year
having count(distinct case when a.year-b.year=1 then year end)>0
and count(distinct case when a.year-b.year=2 then year end)>0
试试吧呵呵
⑥ 如何用 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语句查询出不同年份的总量，求解！！！
没看出来什么数据库
selectyear(publish_time),count(*)from表名groupbyyear(publish_time)
你先看这样报错不
⑧ sql2005中 查询近10年的数据 数据年份可能不连续，数据可能会缺失如何查询
select * from 表
where datediff(year,time,getdate())<=10 就可以,
数据缺失的会自动过滤掉.
⑨ 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查询日期为连续的数据
/*求连续旷工三天以上的数据*/
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