Table: Nombre
With Number as (
select B,count(B) as count
from Number
where 1=1
group by B
)
, Number2 As (
select B
from Number
where count>1
, Data As (
select * from Data where B in (select * from Number2)
)
3 Réponses :
Vous pouvez utiliser index sur la colonne B dans les deux la table vous aidera à réduire le coût de la requête. groupe par et ayant la clause comme suit:
Que diriez-vous de rejoindre? Les lignes n ° 1 - 15 représentent des données d'échantillonnage (vous ne tapez pas cela); La requête que vous avez besoin commence à la ligne n ° 16.
SQL> with
2 tnumber (a, b) as
3 (select 12, 34 from dual union all
4 select 22, 34 from dual union all
5 select 11, 35 from dual union all
6 select 13, 36 from dual
7 ),
8 tdata (b, c, d) as
9 (select 34, 'a', 'b' from dual union all
10 select 34, 'c', 'd' from dual union all
11 select 35, 'e', 'f' from dual union all
12 select 35, 'g', 'h' from dual union all
13 select 36, 'i', 'j' from dual union all
14 select 36, 'k', 'l' from dual
15 )
16 select d.*
17 from tdata d join (select n.b
18 from tnumber n
19 group by n.b
20 having count(*) > 1
21 ) x
22 on d.b = x.b;
B C D
---------- - -
34 a b
34 c d
SQL>
Essayez:
SELECT D.* FROM(SELECT B FROM Number GROUP BY B HAVING COUNT(B) > 1)TEMP JOIN DATA D ON D.B = TEMP.B