① 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