oracle SQL Query to Count() 多个表

SQL Query to Count() multiple tables

提问by macleojw

I have a table which has several one to many relationships with other tables. Let's say the main table is a person, and the other tables represent pets, cars and children. I would like a query that returns details of the person,the number of pets, cars and children they have e.g.

我有一个表，它与其他表有几个一对多的关系。假设主表是一个人，其他表代表宠物、汽车和儿童。我想要一个查询，返回此人的详细信息，他们拥有的宠物、汽车和孩子的数量，例如

Person.Name   Count(cars) Count(children) Count(pets)

John Smith    3           2               4
Bob Brown     1           3               0

What is the best way to do this?

做这个的最好方式是什么？

采纳答案by OMG Ponies

Subquery Factoring (9i+):

子查询分解 (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

Using inline views:

使用内联视图：

   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

回答by Vincent Malgrat

you could use the COUNT(distinct x.id)synthax:

你可以使用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

回答by Eric Petroelje

I would probably do it like this:

我可能会这样做：

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

回答by Dirk

Note, that it depends on your flavour of RDBMS, whether it supports nested selects like the following:

请注意，这取决于您的 RDBMS 风格，它是否支持嵌套选择，如下所示：

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, this works at least with PostgreSQL and MSSQL. Not tested, so your mileage may vary.

IIRC，这至少适用于 PostgreSQL 和 MSSQL。未经测试，因此您的里程可能会有所不同。

回答by Alexey Sviridov

Using subselects not very good practice, but may be here it will be good

使用子选择不是很好的做法，但可能在这里会很好

select p.name, (select count(0) from cars c where c.idperson = p.idperson), 
               (select count(0) from children ch where ch.idperson = p.idperson),
               (select count(0) from pets pt where pt.idperson = p.idperson)
  from person p

回答by Doug Hays

You could do this with three outer joins:

你可以用三个外连接来做到这一点：

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

I belive that Oracle now supports the case whensyntax, but if not you could use a decode.

我相信 Oracle 现在支持case when语法，但如果不支持，您可以使用解码。

回答by Pete OHanlon

You'd need to include multiple count statements in the query. Off the top of my head,

您需要在查询中包含多个计数语句。离开我的头顶，

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