現有一張停電資訊表，有CIRCUIT_NAME停電線路、START_TIME停電開始時間、END_TIME停電結束時間、ORGNAME供電所、SPORGNAME市州公司、PORGNAME區縣公司、EQNM設備名稱、DEV_POWER_CUT停電設備等欄位，現在要查詢兩個月內線路停電三次及三次以上的停電資訊，這個SQL要怎么寫？望大佬們指教。表具體欄位如下：
uj5u.com熱心網友回復：
是距指定時間點2個月內，還是任意2個月的時間間隔內大于2次的？uj5u.com熱心網友回復：
任意的兩個月，這兩個月是動態的，不是指定的。uj5u.com熱心網友回復：
我寫了一個SQL，但是沒有把任意兩個月的條件加入進去。select t1.*, t2.cs
from (select porgname, sporgname, circuit_name, eqnm, start_time, end_time
from t_pw_tdxx) t1
left join (select eqnm, count(*) cs
from t_pw_tdxx
where (circuit_name like '%線' or circuit_name like '%線路' or
circuit_name like '%桿斷路器')
or (eqnm like '%線' or eqnm like '%線路' or eqnm like '%桿斷路器')
and ceil(to_number(SUBSTR(to_char((end_time - start_time) * 24 * 60),
2,
10))) >= 0
group by eqnm) t2
on t1.eqnm = t2.eqnm
where t2.cs >= 3
uj5u.com熱心網友回復：
with tab1 as(
select 'a' id, date'2020-01-02' dat from dual union all
select 'a' id, date'2020-01-03' from dual union all
select 'a' id, date'2020-01-04' from dual union all
select 'a' id, date'2020-01-05' from dual union all
select 'b' id, date'2020-01-02' from dual union all
select 'b' id, date'2020-04-02' from dual
)
, tab2 as (
select t1.*,
count(1) over(partition by t1.id order by t1.dat range between interval '0' month preceding and interval '2' month following) cot
from tab1 t1
)
select t1.id from tab2 t1
group by t1.id
having count(case when t1.cot > 2 then 1 else null end) > 0
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/263061.html
標籤：開發