Use oracle SQL of over three and how to find the record-CodePudding

Such as:
Id num1 num2 num3 num4 num5
1, 3, 4, 6 to 10 1001
1002 8, 4, 5, 2 10
1003 1 5 6 7 11
1004 3 9 7 June 2
1005 5 6 7 8 9
1006 4 5 6 7 11

Find out with 1006 4 5 6 7 11 repeat Numbers 3 and above, there are 1003, and 1005 two records, how shall we write the SQL, thank you!

CodePudding user response:

 
With a as 
(select 1001 id, 1 num1, 3 num2, 4 num3, num4 6, 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) the from 
(the select distinct t1. Id, t1. Num1 
The from t1, b b t2, t3 b 
Where a t1. Num1=t2. Num1 
And t2. Num1=t3. Num1 
And t1. Id & lt;> T2. Id 
And t2. Id & lt;> T3. Id 
And t1. Id & lt;> T3. Id) 
Group by id having count (1) & gt;=3 
;

CodePudding user response:

 
With a as 
(select 1001 id, 1 num1, 3 num2, 4 num3, num4 6, 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 
) 
The select t2. Id, count (distinct t2. Num1) 
The from b t1, t2 b 
Where a t1. Num1=t2. Num1 
And t1. Id & lt;> T2. Id 
And t1. Id=1006 
Group by t2. Id having count (distinct t2. Num1) & gt;=3 
;

Specifies the id=1006, 1003 and 1005

CodePudding user response:

refer to the second floor js14982 response:

 
With a as 
(select 1001 id, 1 num1, 3 num2, 4 num3, num4 6, 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 
) 
The select t2. Id, count (distinct t2. Num1) 
The from b t1, t2 b 
Where a t1. Num1=t2. Num1 
And t1. Id & lt;> T2. Id 
And t1. Id=1006 
Group by t2. Id having count (distinct t2. Num1) & gt;=3 
;

Specifies the id=1006, 1003, and 1005

Hello, thank you very much for reply, but may I express enough detail, let you understand the wrong. In fact the record of the above I am just a small part lists, actually database is far more than these, so is unlikely to each record into a table join. Is there a way to achieve?

I began to train of thought is:
The select t2. Id, t2. Col from (
The select t.a as id, (', '| | t.n um1 | |', '| | t.n um2 | |', '| | t.n um3 | |', '| | t.n um4 | |', '| | t.n um5) as col from tes t) t2
Where t2. Col like '%, 5% and t2. The col... But as if also won't work
)

CodePudding user response:

You mean more than the 5 row (num1, num2... )?

CodePudding user response:

The

reference 4 floor js14982 reply:

you mean more than the 5 row (num1, num2... )?

Column is the five columns, rows.

CodePudding user response:

reference 5 floor piaofeer reply:

Quote: refer to 4th floor js14982 response:

You mean more than the 5 row (num1, num2... )?

Column is the five columns, rows.

into column calculation again, even more, but some good calculation, you can use line also didn't I think of how to implement, saying how much volume?

CodePudding user response:

Above (11. G version)

 
With a as 
(select 1001 id, 1 num1, 3 num2, 4 num3, num4 6, 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 the as (select id, ', '| | t.n um1 | |', '| | t.n um2 | |', '| | t.n um3 | |', '| | t.n um4 | |', '| | t.n um5 | |', 'nn the from a where id=1006) 
Select a. * from a where clause regexp_count (t.n n ', '| | a.n um1 | |', ') + regexp_count (t.n n ', '| | a.n um2 | |', ') + regexp_count (t.n n ', '| | a.n um3 | |', ') + 
Regexp_count (t.n n, ', '| | a.n um4 | |', ') + regexp_count (t.n n ', '| | a.n um5 | |', ') & gt;=3 and Anderson d<> T.i d;

CodePudding user response:

Rectify the

 
With a as 
(select 1001 id, 1 num1, 3 num2, 4 num3, num4 6, 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), 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull