Comment sélectionner les parents et les enfants de lignes dans SQL Server

Je veux sélectionner tous les itens liées à user_id 53 (parents et enfants) à partir du tableau suivant. Il devrait être: 1, 2, 4, 8, 9.

my_table
--------------------------------------------
id    parent_id   user_id   sequence   depth
--------------------------------------------
1     null        50        1          1
2     1           52        1.2        2
3     1           52        1.3        2
4     2           53        1.2.4      3
5     2           52        1.2.5      3
6     3           52        1.3.6      3
7     3           51        1.3.7      3
8     4           51        1.2.4.8    4
9     4           51        1.2.4.9    4

Avec CTE j'ai pu sélectionner tous les enfants ou les parents, mais je ne pouvais pas sélectionner les enfants et les parents avec une seule requête. Ci-dessous la cte je suis aide à sélectionner les enfants.

Élément et les enfants

with cte as (
    select t.id, t.parent_id, t.user_id 
    from my_table t 
    where t.user_id=53

    union all

    select t.id, t.parent_id, t.user_id 
    from my_table t
    inner join cte c on (c.parent_id=t.id)
)
select t.* from cte t;

Élément et les parents

with cte as (
    select t.id, t.parent_id, t.user_id 
    from my_table t 
    where t.user_id=53

    union all

    select t.id, t.parent_id, t.user_id 
    from my_table t
    inner join cte c on (c.id=t.parent_id)
)
select t.* from cte t;

Grâce.

InformationsquelleAutor Diogo Arenhart | 2013-03-04
  1. 4

    C'est très pratique que vous avez les séquences. Les parents ont une séquence correspondant à un sous-ensemble initial de la personne que vous cherchez. Le même est vrai pour les enfants, mais dans le sens inverse.

    Suivantes se rapproche de ce que vous voulez:

    select mt.*
from (select sequence from my_table where USER_ID = 53) theone join
     my_table mt
     on mt.sequence like theone.sequence+ '%' or
        theone.sequence like mt.sequence + '%'

    Cependant, vous devez être prudent avec les 10 correspondant 1, par exemple. Donc, nous allons ajouter un délai supplémentaire, le cas échéant:

    select mt.*
from (select sequence from my_table where USER_ID = 53) theone join
     my_table mt
     on mt.sequence like theone.sequence+ '.%' or
        theone.sequence like mt.sequence + '.%'
    • la bonne solution, Gordon. Vous avez juste oublié d'ajouter la ligne avec user_id=53: "sur le mont.id=theone.id ou mt.séquence comme theone.séquence+ '.%' ou theone.séquence comme mt.séquence + '.%'"
    • de penser comme vous, nous pouvons aussi le faire en utilisant existe: sélectionnez mt.* de ma_table mt où exists(select id from ma_table theone où user_id=53 et (mt.id=theone.id ou mt.séquence comme theone.séquence+ '.%' ou theone.séquence comme mt.séquence + '.%'))
    • Oui, je pense que quelque chose comme ça marcherait aussi bien.
    InformationsquelleAutor Gordon Linoff
  2. 0

    Le problème ici est que vous voulez un récursive SELECT. Un SGBD n'est pas fait pour une telle requête. Mais il est bien sûr possible. 

    SELECT t1.* FROM Table1 t1 WHERE user_id = 53

UNION ALL

SELECT t2.* FROM Table1 t1, Table1 t2
WHERE
  (t1.id = t2.parent_id OR t1.parent_id = t2.id) AND t1.user_id = 53

    Cette requête devrait vous donner chaque 1er degré.

    Pour un appel récursif à SÉLECTIONNER, jetez un oeil ici: Récursive select en SQL

    Solution pour votre requête ici: http://sqlfiddle.com/#!6/e1542/10/0

    with cte as (
    select t.id, t.parent_id, t.user_id 
    from Table1 t 
    where t.user_id=53
),

cte1 as (
    select t.id, t.parent_id, t.user_id 
    from cte t

    union all

    select t.id, t.parent_id, t.user_id 
    from Table1 t
    inner join cte1 c on (c.parent_id=t.id)
),

cte2 as (
    select t.id, t.parent_id, t.user_id 
    from cte t

    union all

    select t.id, t.parent_id, t.user_id 
    from Table1 t
    inner join cte2 c on (c.id=t.parent_id)
)

select t.* from cte1 t

UNION

select t.* from cte2 t
    • Un CTE peut être utilisé comme une requête récursive. Et les deux requêtes Diogo est à l'aide de de récursive.
    • oui il peut, je le sais. Mais c'est épuisant pour la base de données 😉 .. j'ai aussi posté la solution avec un sql violon. ... Et si vous aviez lu les 3 premières phrases de ma réponse, vous auriez vu les mots But it's of course possible.
    • Mais vous avez aussi écrit: "UN SGBD n'est pas fait pour une telle requête" qui n'est pas vrai. Et la première déclaration n'est pas quel Diogo veut (désolé je ne peux pas renoncer à mon downvote - je ne reçois "est pas enfermé dans").
    • Il a certainement pas fait pour ce genre d'opération. Il peut causer des centaines de milliers de requêtes SELECT (comprenant chacune une JOINTURE), si la hiérarchie de l'arbre grandit.
    • Un appel récursif de la procédure stockée sera moins efficace. Et cela dépend du SGBD. Oracle est récursif de traitement est extrêmement rapide.
    • J'ai seulement travaillé avec Postgres et MySQL si loin ... et la question de l'auteur fonctionne avec MS SQL Server. C'est peut-être plus rapide que MySQL, ce qui ne peut certainement pas traiter de grands volumes de récurrences que bien.
    • MySQL ne peut pas faire de requêtes récursives à tous.
    • Désolé, je voulais dire Postgres, qui prend en charge CTE 🙂
    • Il fonctionne vraiment, Benjamin. Je vais utiliser la solution de Gordon simplement parce que c'est plus petit et je ne pouvais pas remarqué de différence significative dans les performances. Si je n'avais pas la séquence de la colonne je pense que ce serait la seule façon de le faire. Je vous remercie.

    InformationsquelleAutor Benjamin M