如:
id num1 num2 num3 num4 num5
1001 1 3 4 6 10
1002 8 4 5 2 10
1003 1 5 6 7 11
1004 3 9 7 6 2
1005 5 6 7 8 9
1006 4 5 6 7 11
找出與1006 4 5 6 7 11有重復數字3個及以上的,有1003,和1005兩條記錄,SQL該如何寫呢,謝謝!
uj5u.com熱心網友回復：
with a as
(select 1001 id,1 num1,3 num2,4 num3,6 num4,10 num5 from dual union all
select 1002,8,4,5,2,10 from dual union all
select 1003,1,5,6,7,11 from dual union all
select 1004,3,9,7,6,2 from dual union all
select 1005,5,6,7,8,9 from dual union all
select 1006,4,5,6,7,11 from dual)
,b as(
select id,num1 from a union all
select id,num2 from a union all
select id,num3 from a union all
select id,num4 from a union all
select id,num5 from a
)
select id,count(1) from
(select distinct t1.id,t1.num1
from b t1,b t2,b t3
where t1.num1 = t2.num1
and t2.num1 = t3.num1
and t1.id <> t2.id
and t2.id <> t3.id
and t1.id <> t3.id)
group by id having count(1)>=3
;
uj5u.com熱心網友回復：
with a as
(select 1001 id,1 num1,3 num2,4 num3,6 num4,10 num5 from dual union all
select 1002,8,4,5,2,10 from dual union all
select 1003,1,5,6,7,11 from dual union all
select 1004,3,9,7,6,2 from dual union all
select 1005,5,6,7,8,9 from dual union all
select 1006,4,5,6,7,11 from dual)
,b as(
select id,num1 from a union all
select id,num2 from a union all
select id,num3 from a union all
select id,num4 from a union all
select id,num5 from a
)
select t2.id,count(distinct t2.num1)
from b t1,b t2
where t1.num1 = t2.num1
and t1.id <> t2.id
and t1.id=1006
group by t2.id having count(distinct t2.num1)>=3
;
指定了id=1006，找1003和1005
uj5u.com熱心網友回復：
你好,非常感謝答復,但可能我表達得不夠明細,讓你理解錯了.其實上面的記錄我只是羅列了一小部分,其實資料庫遠遠不止這些,所以不大可能每條記錄拆分成一個表聯接.有沒有辦法實作?
我開始的思路是:
select t2.id,t2.col from (
select t.a as id , (',' || t.num1||','||t.num2||','||t.num3||','||t.num4||','||t.num5) as col from tes t ) t2
where t2.col like '%,5%' and t2.col.....但好像也行不通
)
uj5u.com熱心網友回復：
你是說列不止這5列（num1，num2...） ？uj5u.com熱心網友回復：
列是這5列,行不止.
uj5u.com熱心網友回復：
就轉成列再計算吧，哪怕列多一些，不過好計算一些。你就用行我也沒想到怎么實作。話說多少資料量？uj5u.com熱心網友回復：
（要求11.g版本以上）
with a as
(select 1001 id,1 num1,3 num2,4 num3,6 num4,10 num5 from dual union all
select 1002,8,4,5,2,10 from dual union all
select 1003,1,5,6,7,11 from dual union all
select 1004,3,9,7,6,2 from dual union all
select 1005,5,6,7,8,9 from dual union all
select 1006,4,5,6,7,11 from dual),
t as (select id,',' || t.num1||','||t.num2||','||t.num3||','||t.num4||','||t.num5||',' nn from a where id=1006)
select a.* from a where regexp_count(t.nn,','||a.num1||',')+regexp_count(t.nn,','||a.num2||',')+regexp_count(t.nn,','||a.num3||',')+
regexp_count(t.nn,','||a.num4||',')+regexp_count(t.nn,','||a.num5||',')>=3 and a.id<>t.id;
uj5u.com熱心網友回復：
糾正一下
with a as
(select 1001 id,1 num1,3 num2,4 num3,6 num4,10 num5 from dual union all
select 1002,8,4,5,2,10 from dual union all
select 1003,1,5,6,7,11 from dual union all
select 1004,3,9,7,6,2 from dual union all
select 1005,5,6,7,8,9 from dual union all
select 1006,4,5,6,7,11 from dual),
t as (select id,',' || num1||','||num2||','||num3||','||num4||','||num5||',' nn from a where id=1006)
select a.* from a where regexp_count(t.nn,','||a.num1||',')+regexp_count(t.nn,','||a.num2||',')+regexp_count(t.nn,','||a.num3||',')+
regexp_count(t.nn,','||a.num4||',')+regexp_count(t.nn,','||a.num5||',')>=3 and a.id<>t.id;
uj5u.com熱心網友回復：
關于 regexp_count() 的說明：
具體詳見官網：http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_regexp.htm#ADFNS9999
uj5u.com熱心網友回復：
有個問題，就是若id=1006的記錄中有重復時會出現錯報，如 1006,4,5,6,7,4，則上述陳述句中會將1001也報出來，還是應該用case來處理：
with a as
(select 1001 id,1 num1,3 num2,4 num3,6 num4,10 num5 from dual union all
select 1002,8,4,5,2,10 from dual union all
select 1003,1,5,6,7,11 from dual union all
select 1004,3,9,7,6,2 from dual union all
select 1005,5,6,7,8,9 from dual union all
select 1006,4,5,6,7,11 from dual),
t as (select id,',' || num1||','||num2||','||num3||','||num4||','||num5||',' nn from a where id=1006)
select a.* from a where case when instr(t.nn,','||a.num1||',')>0 then 1 else 0 end +
case when instr(t.nn,','||a.num2||',')>0 then 1 else 0 end +
case when instr(t.nn,','||a.num3||',')>0 then 1 else 0 end +
case when instr(t.nn,','||a.num4||',')>0 then 1 else 0 end +
case when instr(t.nn,','||a.num5||',')>0 then 1 else 0 end >=3 and a.id<>t.id;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/117707.html
標籤：開發
上一篇：存盤程序
下一篇：求教sqlldr問題