Lister les combinaisons possible

Version imprimable

17/01/2020, 16h53
20cils

Lister les combinaisons possible

Bonjour

je tourne un peu en rond car je débute un peu, faut bien un jour :D

J'ai une table avec 2 colonnes A et B qui ont ces données :

A | 1
A | 2
A | 3
A | 4
B | 5
B | 6
C | 7
D | 8
D | 9
etc ......

j'aimerai obtenir

A 1 2
A 1 3
A 1 4
A 2 1
A 2 3
A 2 4
A 3 1
A 3 2
A 3 4
A 4 1
A 4 2
A 4 3
B 5 6
B 6 5
D 8 9
D 9 8

Donc pour chaque code de la colonne A, je dois avoir toutes les possibilités. Par exemple pour le code B, j'ai 2 lignes (5 et 6) dans la colonne B je doit donc avoir le résultat 5 | 6 et 6 | 5

Par exemple A c'est Plaquettes de frein et les données 1 à 4 ce sont des référence de fournisseurs compatibles entre eux. Donc quand je chercherai la ref 1 cela me proposera les ref 2,3 et 4 aussi vu que j'aurai 1 | 2, 1 | 3 et 1 | 4 de défini.

C'est tout bêtement un produit cartésien dont on retire les doublons...

Code:

1
2
3
4
5
6
7
8
9
select  tb1.col_a
    ,   tb1.col_b
    ,   tb2.col_b
from    matable tb1
    inner join
        matable tb2
        on  tb1.col_a   = tb2.col_a
        and tb1.col_b  <> tb2.col_b
;

20/01/2020, 10h50
20cils

Bonjour et merci

Je n'ai qu'une table et les différentes valeurs sont dans la même colonne 8-)

et je ne veux pas avoir 1 | 5 car 1 est une ref de l'article A et 5 de l'article B
et en plus de faire remonter 1 | 2, il faut aussi 2 | 1
20/01/2020, 11h11
al1_24

Et la requête que je proposais ne répond pas au besoin ?
20/01/2020, 11h52
20cils

Il faut 2 tables pour faire une jointure.

C'est une sorte de produit cartesien mais sur une meme colonne, uniquement sur les données de la colonne B ayant la même donnée A
20/01/2020, 11h55
al1_24

Citation:

Envoyé par 20cils

Il faut 2 tables pour faire une jointure.

Ou 2 alias pour la même table :roll:

Faut tester le code fourni

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH MATABLE  AS (
					SELECT 'A' col_a, 1 col_b FROM dual
UNION ALL SELECT 'A' col_a, 2 col_b FROM dual
UNION ALL SELECT 'A' col_a, 3 col_b FROM dual
UNION ALL SELECT 'A' col_a, 4 col_b FROM dual
UNION ALL SELECT 'B' col_a, 5 col_b FROM dual
UNION ALL SELECT 'B' col_a, 6 col_b FROM dual
UNION ALL SELECT 'C' col_a, 7 col_b FROM dual
UNION ALL SELECT 'D' col_a, 8 col_b FROM dual
UNION ALL SELECT 'D' col_a, 9 col_b FROM dual)
SELECT  tb1.col_a, tb1.col_b, tb2.col_b
FROM MATABLE tb1
		INNER JOIN MATABLE  tb2 ON tb1.col_a = tb2.col_a AND tb1.col_b  <> tb2.col_b
order by 1,2,3

Résultat

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
COL_A	COL_B	COL_B_1
A	1	2
A	1	3
A	1	4
A	2	1
A	2	3
A	2	4
A	3	1
A	3	2
A	3	4
A	4	1
A	4	2
A	4	3
B	5	6
B	6	5
D	8	9
D	9	8

20/01/2020, 14h03
20cils

j'ai 24 000 lignes dans la table ca va etre long :mrgreen:
20/01/2020, 15h17
escartefigue

Il y a un moment où il est bon de tester les solutions proposées, sans quoi les contributeurs finissent par se lasser...
20/01/2020, 15h43
20cils

C’est pas que je ne veux pas mais je n’ai pas de tb2, je n’ai pas de ABCDE mais des milliers de lignes, je n’ai pas de 123456789 mais 24000 lignes.
Si je laisse du tb2 j’ai de l’identificateur non valide

Si vous n'aimez pas les jointures, vous pouvez faire comme ceci :
Données

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create table matable
( col_a     char(1)
, col_b     number(1)
);
 
insert into matable (col_a, col_b)
select 'A', 1 from dual union all
select 'A', 2 from dual union all
select 'A', 3 from dual union all
select 'A', 4 from dual union all
select 'B', 5 from dual union all
select 'B', 6 from dual union all
select 'C', 7 from dual union all
select 'D', 8 from dual union all
select 'D', 9 from dual;
 
commit;

Requête

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
with cte_matable as
(
select col_a
     , col_b
     , min(col_b) over(partition by col_a) as col_b_min
     , max(col_b) over(partition by col_a) as col_b_max
  from matable
)
     select col_a
          , col_b
          , col_b_min + level - 1   as col_c
       from cte_matable
      where col_b_min + level - 1   <> col_b
 connect by prior col_a              = col_a
        and prior col_b              = col_b
        and prior dbms_random.value is not null
        and col_b_max - col_b_min   >= level - 1
   order by col_a, col_b, col_c;
 
COL_A  COL_B  COL_C
-----  -----  -----
    A      1      2
    A      1      3
    A      1      4
    A      2      1
    A      2      3
    A      2      4
    A      3      1
    A      3      2
    A      3      4
    A      4      1
    A      4      2
    A      4      3
    B      5      6
    B      6      5
    D      8      9
    D      9      8

20/01/2020, 15h57
vanagreg

Bonjour,

C'est peut-être parce que tu n'as pas lu le point 5) des règles du forum SQL, dont le lien est donné dans la signature de al1_24.
Idéalement il faut fournir l'ordre DDL de création de ta table avec un petit jeu de données significatif. Ici tb2 c'est juste le 2è alias utilisé pour faire le join sur ta table. Si tu remplaces MATABLE par le nom de ta vraie table et les colonnes par le nom de tes vraies colonnes la requête devrait fonctionner.
20/01/2020, 15h59
Waldar

Citation:

Envoyé par 20cils

je n’ai pas de ABCDE mais des milliers de lignes, je n’ai pas de 123456789 mais 24000 lignes.

24000 lignes ce n'est rien du tout - même si votre serveur de base de données avait 20 ans.

Edit : je mets un peu d'eau dans mon vin, c'est la largeur des intervalles que vous souhaitez propager qui va être le point limitant.
Si c'est 10, ça ira, si c'est 1 million effectivement vous allez avoir un problème mais dans ce cas c'est votre solution complète qu'il faudra revoir.

Citation:

Envoyé par 20cils

Si je laisse du tb2 j’ai de l’identificateur non valide

Vous devez comprendre que nous n'avons pas accès à votre base de données et qu'on a fait ici une interprétation de votre problème avec le jeu de données que vous nous avez fourni.
Il vous faut bien entendu faire la transposition inverse de la solution avec vos vraies tables et colonnes.
20/01/2020, 16h06
McM

Il est aussi possible que le WITH ait perturbé.
Le WITH permet de générer à la volée une vue temporaire avec des données (équivalent des ordres CREATE TABLE, INSERT), donc bien sûr cette partie n'est pas à reprendre avec la vraie table.
20/01/2020, 16h20
20cils

Je ne dis pas que c’est beaucoup, je dis que je ne peux pas faire un script en écrivant moi même les milliers de référence à la place de ABCD et les 24000 à la place de 123456789

Le résultat est bon mais il a été fait en utilisant de la donnée à l’intérieur du script.
20/01/2020, 16h23
20cils

Citation:

Envoyé par vanagreg

Bonjour,

C'est peut-être parce que tu n'as pas lu le point 5) des règles du forum SQL, dont le lien est donné dans la signature de al1_24.
Idéalement il faut fournir l'ordre DDL de création de ta table avec un petit jeu de données significatif. Ici tb2 c'est juste le 2è alias utilisé pour faire le join sur ta table. Si tu remplaces MATABLE par le nom de ta vraie table et les colonnes par le nom de tes vraies colonnes la requête devrait fonctionner.

Si si j’ai bien tout remplacé
20/01/2020, 16h29
20cils

Citation:

Envoyé par McM

Il est aussi possible que le WITH ait perturbé.
Le WITH permet de générer à la volée une vue temporaire avec des données (équivalent des ordres CREATE TABLE, INSERT), donc bien sûr cette partie n'est pas à reprendre avec la vraie table.

Merci pour l’explication

Peut être, je testerai avec le create de waldar pour voir ce que cela donne.
20/01/2020, 16h41
Waldar

Citation:

Envoyé par 20cils

Peut être, je testerai avec le create de waldar pour voir ce que cela donne.

Le create table c'est pour simuler le jeu de données que vous avez fourni au premier message.
Vous n'en n'avez pas besoin.

Bin oui, j’avais pas fait le rapprochement avec le with, j’étais parti sur la création d’une table intermédiaire, avec l’Internet KO et le téléphone ça m’a pas aidé.

Je pars demain sur
Code:

1 2 3 4 SELECT tb1.REF_STOCK, tb1.REF_FOURNISSEUR, tb2.REF_FOURNISSEUR FROM STOCK tb1 INNER JOIN STOCK tb2 ON tb1.REF_STOCK = tb2.REF_STOCK AND tb1.REF_FOURNISSEUR <> tb2. REF_FOURNISSEUR order by 1,2,3
On verra si il prend bien les 2 alias sur ma table STOCK.

22/01/2020, 16h50
20cils

Hello

J'ai un peu lutté pour mettre tout ça dans un insert into (sequence, valeurs fixes) mais c'est tout bon :D

merci