如有以下的tableA:
id name start_date end_date
1 Amy 8/1/2019 9/23/2019
2 Bob 8/2/2019 9/24/2019
3 Cathy 8/3/2019 9/25/2019
4 Amy 10/4/2019 10/26/2019
5 Amy 8/5/2019 9/29/2019
怎么樣查出name重復，并且時間有重疊的資料？
應該查出上例中第一條和第五條資料（因為第四條資料沒有與其他name為Amy的時間有重疊）：
id name start_date end_date
1 Amy 8/1/2019 9/23/2019
5 Amy 8/5/2019 9/29/2019
uj5u.com熱心網友回復：
自連接就行
WITH TMP AS
(SELECT 1 ID, 'Amy' AS NAME, TO_DATE('8/1/2019', 'mm/dd/yyyy') AS START_DATE,
TO_DATE('9/23/2019', 'mm/dd/yyyy') AS END_DATE
FROM DUAL
UNION ALL
SELECT 2, 'Bob' AS NAME, TO_DATE('8/2/2019', 'mm/dd/yyyy'),
TO_DATE('9/24/2019', 'mm/dd/yyyy')
FROM DUAL
UNION ALL
SELECT 3, 'Cathy' AS NAME, TO_DATE('8/3/2019', 'mm/dd/yyyy'),
TO_DATE('9/25/2019', 'mm/dd/yyyy')
FROM DUAL
UNION ALL
SELECT 4, 'Amy' AS NAME, TO_DATE('10/4/2019', 'mm/dd/yyyy'),
TO_DATE('10/26/2019', 'mm/dd/yyyy')
FROM DUAL
UNION ALL
SELECT 5, 'Amy' AS NAME, TO_DATE('8/5/2019', 'mm/dd/yyyy'),
TO_DATE('9/29/2019', 'mm/dd/yyyy')
FROM DUAL)
SELECT A.*
FROM TMP A
INNER JOIN TMP B
ON B.ID <> A.ID
AND B.NAME = A.NAME
AND B.START_DATE <= A.END_DATE
AND B.END_DATE >= A.START_DATE
ORDER BY A.ID
uj5u.com熱心網友回復：
謝謝！如果實際的TableA的資料量特別大，不能一一列出來的話呢？uj5u.com熱心網友回復：
分析函式，用這兩個作為一個維度，然后countuj5u.com熱心網友回復：
這樣的寫法資料是會發散的，多對多的關聯影響了資料的準確性。uj5u.com熱心網友回復：
思路是這樣，先找出name有多條記錄的，然后再看日期是否包含在另外一個里面。這里有個前提，start_date 必須不大于 end_dateWITH TMP AS
(SELECT 1 ID,
'Amy' AS NAME,
TO_DATE('8/1/2019', 'mm/dd/yyyy') AS START_DATE,
TO_DATE('9/23/2019', 'mm/dd/yyyy') AS END_DATE
FROM DUAL
UNION ALL
SELECT 2,
'Bob' AS NAME,
TO_DATE('8/2/2019', 'mm/dd/yyyy'),
TO_DATE('9/24/2019', 'mm/dd/yyyy')
FROM DUAL
UNION ALL
SELECT 3,
'Cathy' AS NAME,
TO_DATE('8/3/2019', 'mm/dd/yyyy'),
TO_DATE('9/25/2019', 'mm/dd/yyyy')
FROM DUAL
UNION ALL
SELECT 4,
'Amy' AS NAME,
TO_DATE('10/4/2019', 'mm/dd/yyyy'),
TO_DATE('10/26/2019', 'mm/dd/yyyy')
FROM DUAL
UNION ALL
SELECT 5,
'Amy' AS NAME,
TO_DATE('8/5/2019', 'mm/dd/yyyy'),
TO_DATE('9/29/2019', 'mm/dd/yyyy')
FROM DUAL),
tmp1 as
(select *
from tmp
where 1 = 1
and exists (select 1
from (select name
from tmp
where 1 = 1
group by tmp.name
having count(1) > 1) mt
where tmp.name = mt.name))
select *
from tmp1 A
where 1 = 1
and exists
(select 1
from tmp1 b
where 1 = 1
and a.id <> b.id
and a.name = b.name
and (a.start_date between b.start_date and b.end_date or
a.end_date between b.start_date and b.end_date))
uj5u.com熱心網友回復：
如果ID是主鍵就不會, 如果沒有主鍵ID,可以ROWNUM作為ID主鍵
uj5u.com熱心網友回復：
emm 如果時間多對多是會有
uj5u.com熱心網友回復：
改成exists 就行了
WITH TMP AS
(SELECT 1 ID, 'Amy' AS NAME, TO_DATE('8/1/2019', 'mm/dd/yyyy') AS START_DATE,
TO_DATE('9/23/2019', 'mm/dd/yyyy') AS END_DATE
FROM DUAL
UNION ALL
SELECT 2, 'Bob' AS NAME, TO_DATE('8/2/2019', 'mm/dd/yyyy'),
TO_DATE('9/24/2019', 'mm/dd/yyyy')
FROM DUAL
UNION ALL
SELECT 3, 'Cathy' AS NAME, TO_DATE('8/3/2019', 'mm/dd/yyyy'),
TO_DATE('9/25/2019', 'mm/dd/yyyy')
FROM DUAL
UNION ALL
SELECT 4, 'Amy' AS NAME, TO_DATE('10/4/2019', 'mm/dd/yyyy'),
TO_DATE('10/26/2019', 'mm/dd/yyyy')
FROM DUAL
UNION ALL
SELECT 5, 'Amy' AS NAME, TO_DATE('8/5/2019', 'mm/dd/yyyy'),
TO_DATE('9/29/2019', 'mm/dd/yyyy')
FROM DUAL
UNION ALL
SELECT 6, 'Amy' AS NAME, TO_DATE('8/5/2019', 'mm/dd/yyyy'),
TO_DATE('9/29/2019', 'mm/dd/yyyy')
FROM DUAL)
SELECT A.*
FROM TMP A
WHERE EXISTS (SELECT 1
FROM TMP B
WHERE B.ID <> A.ID
AND B.NAME = A.NAME
AND B.START_DATE <= A.END_DATE
AND B.END_DATE >= A.START_DATE)
ORDER BY A.ID
uj5u.com熱心網友回復：
通常自連接的效率還是要差一點的，直接分析函式就好WITH tab1 AS
(SELECT 1 ID, 'Amy' AS NAME, TO_DATE('8/1/2019', 'mm/dd/yyyy') AS START_DATE,
TO_DATE('9/23/2019', 'mm/dd/yyyy') AS END_DATE
FROM DUAL
UNION ALL
SELECT 2, 'Bob' AS NAME, TO_DATE('8/2/2019', 'mm/dd/yyyy'),
TO_DATE('9/24/2019', 'mm/dd/yyyy')
FROM DUAL
UNION ALL
SELECT 3, 'Cathy' AS NAME, TO_DATE('8/3/2019', 'mm/dd/yyyy'),
TO_DATE('9/25/2019', 'mm/dd/yyyy')
FROM DUAL
UNION ALL
SELECT 4, 'Amy' AS NAME, TO_DATE('10/4/2019', 'mm/dd/yyyy'),
TO_DATE('10/26/2019', 'mm/dd/yyyy')
FROM DUAL
UNION ALL
SELECT 5, 'Amy' AS NAME, TO_DATE('8/5/2019', 'mm/dd/yyyy'),
TO_DATE('9/29/2019', 'mm/dd/yyyy')
FROM DUAL)
, tab2 as (
select t1.*,
case when
lead(t1.start_date) over(partition by t1.name order by t1.start_date) between t1.start_date and t1.end_date
or
lag(t1.end_date) over(partition by t1.name order by t1.start_date) between t1.start_date and t1.end_date
then 1 else 0 end flag
from tab1 t1
)
select*from tab2 t1 where t1.flag = 1
;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/22933.html
標籤：開發
上一篇：非計算機專業能考dba嗎
下一篇：imp匯入備份實體問題