Início / dba / Perguntas / 194131
Accepted
obautista
Asked: 2017-12-29 12:42:29 +0800 CST

Relação pai-filho na mesma tabela

  • 772

Eu tenho uma tabela armazenando registros pai/filho como tal:

+-------+------------+---------+---------+------------+-----------+
|custid | custname   | deptid  | company |parentcustid| enrolled  |
+=======+============+=========+=========+============+===========+
| 7060  | Sally      |   AB1   | comp1   |  null      |     1     |
| 6953  | Ajit       |   AB7   | comp2   |  7060      |     1     |
| 6957  | Rahul      |   DE1   | comp3   |  7060      |     1     |
| 6958  | uday       |   TG6   | comp4   |  7060      |     1     |
| 6959  | john       |   HY7   | comp5   |  7060      |     1     |
| 6960  | netaji     |   HY5   | comp6   |  7060      |     1     |
| 6961  | prakriti   |   GT6   | comp7   |  7060      |     1     |
| 6962  | sachin     |   KL7   | comp8   |  7060      |     0     |
| 6963  | santosh    |   KK5   | comp9   |  7060      |     1     |
| 6964  | Ravi       |   PP0   | comp10  |  7060      |     1     |
+-------+------------+---------+---------+------------+-----------+

É possível retornar registros em que o registro pai está registrado e o registro filho relacionado não está registrado?

Isso retorna o que eu preciso para 1 cliente específico:

select a.custid, a.custname, a.deptid, a.company, a.parentcustid, a.enrolled
from customer a
where a.company = 'comp1' and a.Enrolled = 1
union all
select a.custid, a.custname, a.deptid, a.company, a.parentcustid, a.enrolled
from customer a
where a.parentcustid= 7060 and b.Enrolled = 0

+-------+------------+---------+---------+------------+-----------+
|custid | custname   | deptid  | company |parentcustid| enrolled  |
+=======+============+=========+=========+============+===========+
| 7060  | Sally      |   AB1   | comp1   |  null      |     1     |
| 6962  | sachin     |   KL7   | comp8   |  7060      |     0     |
+-------+------------+---------+---------+------------+-----------+

Como posso estruturar a consulta para retornar esse tipo de conjunto de resultados para todos os registros pai filho na tabela?

sql-server join

2 respostas

  1. Uma maneira comum de fazer isso no SQL Server é usar um CTE recursivo :

    Aqui estão seus dados de teste como uma inserção:

    CREATE TABLE #yourmom
(
    custid INT,
    custname VARCHAR(10),
    deptid VARCHAR(3),
    company VARCHAR(10),
    parentcustid INT,
    enrolled BIT
)

INSERT #yourmom ( custid, custname, deptid, company, parentcustid, enrolled )
SELECT x.custid, x.custname, x.deptid, x.company, x.parentcustid, x.enrolled
FROM   ( VALUES ( 7060, 'Sally   ', 'AB1', 'comp1 ', NULL, 1 ),
                ( 6953, 'Ajit    ', 'AB7', 'comp2 ', 7060, 1 ),
                ( 6957, 'Rahul   ', 'DE1', 'comp3 ', 7060, 1 ),
                ( 6958, 'uday    ', 'TG6', 'comp4 ', 7060, 1 ),
                ( 6959, 'john    ', 'HY7', 'comp5 ', 7060, 1 ),
                ( 6960, 'netaji  ', 'HY5', 'comp6 ', 7060, 1 ),
                ( 6961, 'prakriti', 'GT6', 'comp7 ', 7060, 1 ),
                ( 6962, 'sachin  ', 'KL7', 'comp8 ', 7060, 0 ),
                ( 6963, 'santosh ', 'KK5', 'comp9 ', 7060, 1 ),
                ( 6964, 'Ravi    ', 'PP0', 'comp10', 7060, 1 )
        ) AS x ( custid, custname, deptid, company, parentcustid, enrolled );

    E aqui está como um CTE recursivo procuraria por ele: 

    WITH yourdad
AS ( SELECT y.custid, y.custname, y.deptid, y.company, y.parentcustid, y.enrolled
     FROM   #yourmom AS y
     WHERE  y.parentcustid IS NULL
            AND y.enrolled = 1
     UNION ALL
     SELECT ym2.custid, ym2.custname, ym2.deptid, ym2.company, ym2.parentcustid, ym2.enrolled
     FROM   yourdad AS yd
     JOIN   #yourmom AS ym2
     ON ym2.parentcustid = yd.custid
        AND ym2.enrolled = 0 )
SELECT *
FROM   yourdad;

    Existem alguns Great Posts® escritos sobre eles:

    Espero que isto ajude!

    • 7
  2. Best Answer

    Se você tiver apenas um nível de filhos, poderá juntar as tabelas

    SELECT
    a.custid, a.custname, a.deptid, a.company,
    b.custid AS bcustid, b.custname AS bcustname, b.deptid AS bdeptid, b.company AS bcompany
FROM
    customer a
    LEFT JOIN customer b
        ON a.custid = b.parentcustid AND b.Enrolled = 0
WHERE
    a.parentcustid  IS NULL AND a.Enrolled = 1

    Assim você não perde a relação entre o registro pai e filho.

    Como você sabe que os pais estão matriculados e os filhos não, retornar a enrolledcoluna não adiciona novas informações.

    Se você quiser manter a forma original da tabela de resultados, use uma coluna adicional para classificar

    SELECT
    custid, custname, deptid, company, parentcustid, enrolled,
    10 * custid AS orderKey
FROM customer
WHERE parentcustid IS NULL AND Enrolled = 1
UNION ALL
SELECT
    custid, custname, deptid, company, parentcustid, enrolled,
    10 * parentcustid + 1 AS orderKey
FROM customer
WHERE parentcustid IS NOT NULL AND Enrolled = 0
ORDER BY orderKey, custid

    Observe que este ORDER BY é aplicado a toda a união, não apenas ao segundo SELECT. Assim as crianças são sempre listadas logo abaixo de seus pais.

    Aliás, multiplicar a chave por 2 é suficiente para obter o efeito desejado para a chave de ordem. Ele só fica melhor se você multiplicar por 10. Se seus IDs de cliente sempre tiverem no máximo 4 dígitos, você também pode usar 10000 * custidpara os pais e 10000 * parentcustid + custidpara os filhos e ordenar apenas por orderKeypara obter registros bem ordenados.

    • 5

relate perguntas