1
votes

SQL Server - compter le nombre de grands-parents vivants

Je suis des bases de données d'apprentissage à domicile et j'essaie actuellement de résoudre le problème suivant:

Sélectionnez l'identifiant et le nom des personnes qui sont des petits-enfants. Sélectionnez également pour chaque personne le nombre de ses grands-parents encore vivants.

Ma table ressemble à ceci:

id, nom, date_of_birth, date_of_death (est NULL lorsque la personne est vivant), sexe, id_père, id_mère

J'ai pu résoudre la partie en sélectionnant l'identifiant et le nom des personnes qui sont des petits-enfants de la manière suivante:

SELECT b.name, b.id
FROM persons a
JOIN persons b ON c.father_id = a.id or a.mother_id = a.id
JOIN persons c on p.father_id = c.id 
WHERE b.father_id = a.id or b.mother_id = a.id;

Cependant, je ne peux pas résolvez la partie avec le nombre de grands-parents encore vivants pour chaque personne.

Pourriez-vous s'il vous plaît m'aider?

Merci

sql sql-server mysql database

1 commentaires

Il est plus facile pour les gens de répondre si vous créez un violon SQL. J'en ai créé un que vous pouvez bifurquer / modifier à votre guise. Je suggère d'inclure ce lien avec votre question. sqlfiddle.com/#!9/441ad0/7

3 Réponses :

0
votes

Vous devez joindre 3 copies de la table, groupe par personne et compter conditionnellement le nombre de grands-parents vivants:

sum(p2.date_of_death is null) living_grandparents

Pour MySql, la condition pourrait être simplifiée à:

XXX

0 commentaires

1
votes

Il s'agit de rejoindre les tables. Je pense que vous êtes tous confus par les alias de table sur les auto-jointures. Donnez-leur donc des alias significatifs :

p pour la personne
pp pour les parents de la personne
gp pour les grands-parents de la personne

Ensuite, une fois que vous avez les JOIN corrects, le reste est principalement de l'agrégation:

SELECT p.name, p.id,
       SUM(CASE WHEN gp.date_of_death IS NULL THEN 1 ELSE 0 END)
FROM persons p JOIN    -- persons
     persons pp        -- parents
     ON pp.id IN (p.father_id, p.mother_id) JOIN
     persons gp
     ON gp.id IN (pp.father_id, pp.mother_id)
GROUP BY p.name, p.id;

Notez la somme conditionnelle qui comprend la date du décès.

1 commentaires

Beaucoup plus élégant que ma réponse. +1

0
votes

Découvrez la solution SQL Fiddle :)

http: // sqlfiddle. com / #! 9 / 441ad0 / 16

    select 
  (
    select 
      count(*) 
    from 
      people 
    where 
      (
        id = p.fatherId 
        or id = p.motherId
      ) 
      and (
        fatherId in (
          select 
            id 
          from 
            people 
          where 
            dod is null
        ) 
        or motherId in (
          select 
            id 
          from 
            people 
          where 
            dod is null
        )
      )
  ) as mycount, 
  p.name, 
  p.id, 
  p.fatherId, 
  p.motherId 
FROM 
  people p 
WHERE 
  p.fatherId in (
    SELECT 
      id 
    from 
      people 
    where 
      id = p.fatherId 
      and fatherId in (
        SELECT 
          id 
        from 
          people 
        where 
          1 = 1
      )
  ) 
  OR p.motherId in (
    SELECT 
      id 
    from 
      people 
    where 
      id = p.motherId 
      and motherId in (
        SELECT 
          id 
        from 
          people 
        where 
          1 = 1
      )
  )

0 commentaires