3
votes

Puis-je créer une condition de jointure qui joint si la clé de jointure a un champ particulier?

J'ai une table qui a une colonne avec des chaînes formatées comme ceci: {1,4,5} . Ils peuvent être de n'importe quelle longueur et j'aimerais joindre une table d'ID par rapport à n'importe quelle valeur qui a son ID dans cette chaîne. est le deuxième tableau -

select id2, name, id, count
from table2 as t2 
left join table1 as t1 
on t2.id2 %in% t1.id

Je voudrais une ligne pour chaque identifiant listé dans le premier tableau qui a la catégorie de la deuxième table. Je voudrais qu'ils ressemblent à ceci -

id2 name     id         count 
1 apple    {1,3,6}    5
1 orange   {5,3,1}    3
1 potato   {8,1,9}    3
3 apple    {1,3,6}    5
3 orange   {5,3,1}    3
8 potato   {8,1,9}    3
9 potato   {8,1,9}    3

C'est ce que j'ai jusqu'à présent. Puis-je avoir un filtre de jointure indiquant rejoindre si la valeur est incluse ?

id2     category
1      foo
2      foobar
3      candy
4      candybar
5      oreo
6      pistachio

postgresql sql

3 commentaires

Les fonctions de chaîne sont très différentes d'une base de données à l'autre. Quelle base de données spécifique utilisez-vous? PostgreSQL, Oracle, DB2, etc.

J'utilise PostgreSQL.

Pour ce que ça vaut, le champ id est créé à partir d'une fonction array_agg () , mais j'ai pensé que je devrais le convertir en texte.

3 Réponses :

5
votes

1) Conseils non sollicités

Je pense que cela vaut la peine de considérer si la conception de votre base de données (c'est-à-dire la façon dont vous coupez vos tables) est vraiment bénéfique pour votre cause. La façon dont les tables sont actuellement configurées enfreint la 1ère forme normale de conception de base de données. Pensez à changer votre conception pour exprimer une relation n: m entre les objets de FirstTable et SecondTable
Avoir des noms valides dans le contexte de la table. Au lieu d'avoir id2 dans une table et id dans une autre, nommez simplement les deux id . Dans vos requêtes, vous pouvez les appeler firsttable.id et secondtable.id pour les distinguer.

2) Réponse réelle

Oui, c'est possible mais (comme l'ont également souligné les commentateurs) dépend du système de base de données que vous utilisez.

Si firststable.id est un tableau dans PostgreSQL, la requête suivante devrait fonctionner:

SELECT
    *
FROM
    first
JOIN
    second
ON
    second.id = ANY(first.ids::int[]);

Ce SQLFiddle fournit un exemple fonctionnel.

Si firsttable.id est une chaîne alors vous pouvez convertir la chaîne en un tableau en utilisant '{42, 23, 17}' :: int [] comme décrit ici :

SELECT
    *
FROM
    first
JOIN
    second
ON
    second.id = ANY(first.ids);
    -- Took the liberty to change the column names

Ceci SQLFiddle donne un exemple fonctionnel au cas où il s'agirait d'une chaîne.

7 commentaires

Je serais d'accord avec vous si les valeurs comme {1,2,3} étaient des tableaux d'entiers. Cependant, il semble qu'ils soient VARCHAR .

Le deuxième SQLFiddle ne gérerait-il pas cela correctement?

J'adapterai la réponse pour rendre cela plus explicite.

Je pense que cela fonctionnerait. +1 pour l'effort. Je vous encourage à utiliser la syntaxe de jointure moderne.

THX! Je ne savais pas si la jointure croisée ou la jointure gauche serait plus appropriée. Quelle est la raison de votre suggestion? Meilleure lisibilité? :)

La syntaxe de jointure moderne sépare les prédicats de jointure (dans la clause JOIN ) des prédicats de filtrage (dans la clause WHERE ). Cela améliore considérablement la lisibilité, le débogage et facilite également la gestion des performances des requêtes.

Je ne suis pas sûr de tout ce qui concerne VARCHAR, mais cela a fait l'affaire pour moi.

0
votes

Je n'ai pas vu PostgreSQL lorsque j'ai commencé à résoudre ce problème.

Vous pouvez essayer ce qui suit, mais aucune garantie si Postgre n'a pas toutes les fonctions.

SELECT * FROM (
     SELECT 
         Split.a.value('.', 'VARCHAR(100)') AS ID2  
         ,A.Name, A.ID, A.[Count]
     FROM  
     (
         SELECT Name, [Count], ID,  
             CAST ('<M>' + REPLACE(REPLACE(REPLACE(ID,'{',''),'}',''), ',', '</M><M>') + '</M>' AS XML) AS Data  
         FROM [StackOver].[dbo].[SplitKey]
     ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a)
 ) as B  
 Left Join [StackOver].[dbo].[SplitKeyID2] as C
 On B.ID2 = C.ID2
  Where C.Category > ''
 Order By B.ID2, B.name

0 commentaires

0
votes

Je suis assez convaincu qu'il existe une meilleure solution qui n'implique pas les GROUP BY et ARRAY_AGG () , mais puisque vous y êtes déjà, je pense que cette requête peut vous aider:

select
  t2.id2,
  t2.category,
  t1.id,
  t1.count
from table1 t1
join table2 t2 on (
     position ('{' || t2.id2 || '}' in t1.id) <> 0
  or position ('{' || t2.id2 || ',' in t1.id) <> 0
  or position (',' || t2.id2 || ',' in t1.id) <> 0
  or position (',' || t2.id2 || '}' in t1.id) <> 0
)

0 commentaires