用oracle SQL如何查找出出现3个及以上的记录

Question

如: 
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该如何写呢,谢谢!

Answer 1

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
;

Answer 2

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

Answer 3

你好,非常感谢答复,但可能我表达得不够明细,让你理解错了.其实上面的记录我只是罗列了一小部分,其实数据库远远不止这些,所以不大可能每条记录拆分成一个表联接.有没有办法实现?我开始的思路是:
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.....但好像也行不通
)

Answer 4

你是说列不止这5列（num1，num2...） ？

Answer 5

（要求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;
 

Answer 6

纠正一下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;
 

Answer 7

关于 regexp_count() 的说明：具体详见官网：http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_regexp.htm#ADFNS9999

Answer 8

有个问题，就是若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;