比如 我在表a里面搜到了n條資料，表a里面有個欄位是時間time，現在按照一定條件id=1，搜到n條資料，把這些資料order by time 按照時間排序，把搜到的資料中 time 間隔不超過60天的資料中的 name code 欄位取出來，請教大神，這條sql怎么寫，開發需要！！
uj5u.com熱心網友回復：
這個間隔是指相鄰記錄間隔不超過60天嗎?uj5u.com熱心網友回復：
with a as
(select 'a' name,1 code,date'2015-1-1' time from dual union all
select 'b' name,2 code,date'2015-1-6' time from dual union all
select 'c' name,3 code,date'2016-1-9' time from dual union all
select 'd' name,4 code,date'2016-2-1' time from dual union all
select 'e' name,5 code,date'2016-3-7' time from dual union all
select 'f' name,6 code,date'2016-8-1' time from dual
),
t as
(select name,code,time,row_number()over(order by time) rn
from a
--where id=1
)
select t1.name,t1.code,t2.*
from t t1,t t2
where t1.rn=t2.rn-1
and (abs(t1.time-t2.time)<=60 or abs(t2.time-t1.time)<=60)
order by 1
;
另外也可以用lead和lag函式來滿足你的需求，具體用法你搜一下或者等#1
uj5u.com熱心網友回復：
有點問題，改一下
[code=sql]with a as
(select 'a' name,1 code,date'2015-1-1' time from dual union all
select 'b' name,2 code,date'2015-1-6' time from dual union all
select 'c' name,3 code,date'2016-1-9' time from dual union all
select 'd' name,4 code,date'2016-2-1' time from dual union all
select 'e' name,5 code,date'2016-3-7' time from dual union all
select 'f' name,6 code,date'2016-8-1' time from dual
),
t as
(select name,code,time,row_number()over(order by time) rn
from a
--where id=1
)
select t1.name,t1.code
from t t1,t t2
where t1.rn=t2.rn-1
and (t1.time-t2.time<=60 or abs(t2.time-t1.time)<=60)
order by 1
;
uj5u.com熱心網友回復：
使用開窗函式：
with a as
(select 'a' name,1 code,date'2015-1-1' time from dual union all
select 'b' name,2 code,date'2015-1-6' time from dual union all
select 'c' name,3 code,date'2016-1-9' time from dual union all
select 'd' name,4 code,date'2016-2-1' time from dual union all
select 'e' name,5 code,date'2016-3-7' time from dual union all
select 'f' name,6 code,date'2016-8-1' time from dual
),
t as (select name,code,time,count(1) over (order by time desc range interval '60' day preceding)
+count(1) over (order by time range interval '60' day preceding) cc from a)
select name,code,time from t where cc>2;
uj5u.com熱心網友回復：
試試分析函式實作吧參考http://blog.csdn.net/thinkscape/article/details/8290894
uj5u.com熱心網友回復：
同問，誰和誰的間隔？
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/106163.html
標籤：開發
上一篇：錯誤提示如何解決
下一篇：資料庫字符集問題