CREATE TABLE A
(
Id NUMBER(10),
Name varchar(5),
Rq DATE
)
資料如下
Id Name Rq
1 A 2020-03-01
2 A 2020-03-31
3 A 2020-04-01
4 A 2020-04-05
5 B 2020-03-21
6 B 2020-03-26
7 C 2020-03-28
8 C 2020-03-30
9 C 2020-04-02
10 C 2020-04-03
11 D 2020-03-29
12 D 2020-03-31
13 D 2020-04-07
我的需求是輸入起止日期，比如20200228，20200406，取出10天以內出現三次以上的資料，期望顯示的資料如下
2 A 2020-03-31
3 A 2020-04-01
4 A 2020-04-05
7 C 2020-03-28
8 C 2020-03-30
9 C 2020-04-02
10 C 2020-04-03
求各位大神給個sql，baidu查了半天，也沒個頭緒
uj5u.com熱心網友回復：
count over，注意range between視窗引數uj5u.com熱心網友回復：
SELECT
*
FROM
(
SELECT
A . ID,
A . NAME,
A .rq,
CASE
WHEN b.cishu >= 3 THEN
'Y'
ELSE
'N'
END AS flag
FROM
A
LEFT JOIN (
SELECT
ID,
COUNT (NAME) AS cishu
FROM
A AS a1
INNER JOIN A AS a2 ON a1. NAME = a2. NAME
AND a1.rq BETWEEN a1.rq - 10
AND a1.rq + 10
GROUP BY
ID
) B ON A . ID = B. ID
)
WHERE
flag = 'Y'
uj5u.com熱心網友回復：
上面那個寫錯了SELECT
*
FROM
(
SELECT
A . ID,
A . NAME,
A .rq,
CASE
WHEN b.cishu >= 3 THEN
'Y'
ELSE
'N'
END AS flag
FROM
A
LEFT JOIN (
SELECT
A1. ID,
COUNT (A2. NAME) AS cishu
FROM
A AS a1
INNER JOIN A AS a2 ON a1. NAME = a2. NAME
AND a1.rq BETWEEN a1.rq - 10
AND a1.rq + 10
WHERE
A1.rq BETWEEN '20200228'
AND '20200406'
GROUP BY
ID
) B ON A . ID = B. ID
)
WHERE
flag = 'Y'
uj5u.com熱心網友回復：
上面那個還是寫錯了，以這個為準，思路應該應該很明確了，就算再錯了，你自己改一下吧。。SELECT
*
FROM
(
SELECT
A . ID,
A . NAME,
A .rq,
CASE
WHEN b.cishu >= 3 THEN
'Y'
ELSE
'N'
END AS flag
FROM
A
LEFT JOIN (
SELECT
A1. ID,
COUNT (A2. NAME) AS cishu
FROM
A AS A1
INNER JOIN A AS A2 ON A1. NAME = A2. NAME
AND A2.rq BETWEEN A1.rq - 10
AND A1.rq + 10
WHERE
A1.rq BETWEEN '20200228'
AND '20200406'
GROUP BY
A1. ID
) B ON A . ID = B. ID
)
WHERE
flag = 'Y'
uj5u.com熱心網友回復：
with tab1 as (
select 1 id, 'a' nam, date'2020-04-01' rq from dual union all
select 2, 'a' nam, date'2020-04-02' rq from dual union all
select 3, 'a' nam, date'2020-04-03' rq from dual union all
select 4, 'a' nam, date'2020-04-15' rq from dual union all
select 5, 'a' nam, date'2020-04-15' rq from dual union all
select 6, 'a' nam, date'2020-04-27' rq from dual union all
select 7, 'a' nam, date'2020-04-28' rq from dual union all
select 8, 'a' nam, date'2020-04-28' rq from dual union all
select 9, 'a' nam, date'2020-05-01' rq from dual
)
, tab2 as (
select t1.*,
row_number() over(partition by t1.nam order by t1.rq, t1.id) rn,
count(1) over(partition by t1.nam order by t1.rq range between current row and 10 following) rang
from tab1 t1
order by t1.rq
)
, tab3 as (
select t1.*,
lag(t1.rang) over(partition by t1.nam order by t1.rn) lag
from tab2 t1
)
, tab4 as (
select t1.*
from tab3 t1
where t1.rang > nvl(t1.lag, -1)
and t1.rang >= 3
)
select t1.id, t1.nam, t1.rq from tab3 t1, tab4 t2
where t1.nam = t2.nam
and t1.rn between t2.rn and t2.rn + t2.rang - 1
order by t1.id
uj5u.com熱心網友回復：
再來一個暴力搜索的with tab1 as (
select 1 id, 'a' nam, date'2020-04-01' rq from dual union all
select 2, 'a' nam, date'2020-04-02' rq from dual union all
select 3, 'a' nam, date'2020-04-03' rq from dual union all
select 4, 'a' nam, date'2020-04-15' rq from dual union all
select 5, 'a' nam, date'2020-04-15' rq from dual union all
select 6, 'a' nam, date'2020-04-27' rq from dual union all
select 7, 'a' nam, date'2020-04-28' rq from dual union all
select 8, 'a' nam, date'2020-04-28' rq from dual union all
select 9, 'a' nam, date'2020-05-01' rq from dual
)
, tab2 as (
select t2.*,
count(1) over(partition by t1.nam, t1.id) cot
from tab1 t1, tab1 t2
where t1.nam = t2.nam
and t1.rq + 10 >= t2.rq
and t1.rq <= t2.rq
order by t1.id, t2.id
)
select distinct t1.id, t1.nam, t1.rq
from tab2 t1 where t1.cot >= 3
;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/13066.html
標籤：開發
上一篇：求教orcale 資料庫 的 多表分頁查詢陳述句該怎么寫呢？
下一篇：求助該如何實作以下這種效果