Tengo una table que tiene varias relaciones uno a muchos con otras tablas. Digamos que la table principal es una persona, y las otras tablas representan mascotas, autos y niños. Me gustaría una consulta que devuelva detalles de la persona, el número de mascotas, automobilees y niños que tienen, por ej.
Person.Name Count (cars) Count (children) Count (mascotas) John Smith 3 2 4 Bob Brown 1 3 0
¿Cuál es la mejor manera de hacer esto?
Factorización de subconsulta (9i +):
WITH count_cars AS ( SELECT t.person_id COUNT(*) num_cars FROM CARS c GROUP BY t.person_id), count_children AS ( SELECT t.person_id COUNT(*) num_children FROM CHILDREN c GROUP BY t.person_id), count_pets AS ( SELECT p.person_id COUNT(*) num_pets FROM PETS p GROUP BY p.person_id) SELECT t.name, NVL(cars.num_cars, 0) 'Count(cars)', NVL(children.num_children, 0) 'Count(children)', NVL(pets.num_pets, 0) 'Count(pets)' FROM PERSONS t LEFT JOIN count_cars cars ON cars.person_id = t.person_id LEFT JOIN count_children children ON children.person_id = t.person_id LEFT JOIN count_pets pets ON pets.person_id = t.person_id
Usando vistas en línea:
SELECT t.name, NVL(cars.num_cars, 0) 'Count(cars)', NVL(children.num_children, 0) 'Count(children)', NVL(pets.num_pets, 0) 'Count(pets)' FROM PERSONS t LEFT JOIN (SELECT t.person_id COUNT(*) num_cars FROM CARS c GROUP BY t.person_id) cars ON cars.person_id = t.person_id LEFT JOIN (SELECT t.person_id COUNT(*) num_children FROM CHILDREN c GROUP BY t.person_id) children ON children.person_id = t.person_id LEFT JOIN (SELECT p.person_id COUNT(*) num_pets FROM PETS p GROUP BY p.person_id) pets ON pets.person_id = t.person_id
puedes usar la COUNT(distinct x.id)
:
SELECT person.name, COUNT(DISTINCT car.id) cars, COUNT(DISTINCT child.id) children, COUNT(DISTINCT pet.id) pets FROM person LEFT JOIN car ON (person.id = car.person_id) LEFT JOIN child ON (person.id = child.person_id) LEFT JOIN pet ON (person.id = pet.person_id) GROUP BY person.name
Probablemente lo haría así:
SELECT Name, PersonCars.num, PersonChildren.num, PersonPets.num FROM Person p LEFT JOIN ( SELECT PersonID, COUNT(*) as num FROM Person INNER JOIN Cars ON Cars.PersonID = Person.PersonID GROUP BY Person.PersonID ) PersonCars ON PersonCars.PersonID = p.PersonID LEFT JOIN ( SELECT PersonID, COUNT(*) as num FROM Person INNER JOIN Children ON Children.PersonID = Person.PersonID GROUP BY Person.PersonID ) PersonChildren ON PersonChildren.PersonID = p.PersonID LEFT JOIN ( SELECT PersonID, COUNT(*) as num FROM Person INNER JOIN Pets ON Pets.PersonID = Person.PersonID GROUP BY Person.PersonID ) PersonPets ON PersonPets.PersonID = p.PersonID
Tenga en count que depende de su sabor de RDBMS, si admite selects anidadas como las siguientes:
SELECT p.name AS name , (SELECT COUNT(*) FROM pets e WHERE e.owner_id = p.id) AS pet_count , (SELECT COUNT(*) FROM cars c WHERE c.owner_id = p.id) AS world_pollution_increment_device_count , (SELECT COUNT(*) FROM child h WHERE h.parent_id = p.id) AS world_population_increment FROM person p ORDER BY p.name
IIRC, esto funciona al less con PostgreSQL y MSSQL. No probado, por lo que su kilometraje puede variar.
Usar subselects no es una buena práctica, pero puede estar aquí, será bueno
select p.nombre, (select conteo (0) de autos c donde c.idperson = p.idperson), (select recuento (0) de los niños ch donde ch.idperson = p.idperson), (select conteo (0) de mascotas pt donde pt.idperson = p.idperson) de la persona p
Puedes hacer esto con tres uniones externas:
SELECT Person.Name, sum(case when cars.id is not null then 1 else 0 end) car_count, sum(case when children.id is not null then 1 else 0 end) child_count, sum(case when pets.id is not null then 1 else 0 end) pet_count FROM Person LEFT OUTER JOIN cars on Person.id = cars.person_id LEFT OUTER JOIN children on Person.id = children.person_id LEFT OUTER JOIN pets on Person.id = pets.person_id GROUP BY Person.Name
Creo que Oracle ahora admite el case when
syntax, pero si no, podrías usar un decodificador.
Tendría que include múltiples declaraciones de conteo en la consulta. La parte superior de mi cabeza,
SELECT p.Name, COUNT(DISTINCT t.Cars), COUNT(DISTINCT o.Children), Count(DISTINCT p.Pets) FROM Person p INNER JOIN Transport t ON p.ID = t.PersonID LEFT JOIN Offspring o ON p.ID = o.PersonID LEFT JOIN Pets p ON p.ID = o.OwnerID GROUP BY p.Name ORDER BY p.Name