SQL Query to Count() 多个表

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

<前>Person.Name Count(cars) Count(children) Count(pets)约翰史密斯 3 2 4鲍勃·布朗 1 3 0

最好的方法是什么?

解决方案

Subquery Factoring (9i+):

WITH count_cars AS (选择 t.person_idCOUNT(*) num_cars从汽车 cGROUP BY t.person_id),count_children AS (选择 t.person_idCOUNT(*) num_children从儿童 cGROUP BY t.person_id),count_pets AS (选择 p.person_idCOUNT(*) num_pets来自宠物 pGROUP BY p.person_id)选择 t.name,NVL(cars.num_cars, 0) 'Count(cars)',NVL(children.num_children, 0) 'Count(children)',NVL(pets.num_pets, 0) 'Count(pets)'从人 tLEFT JOIN count_cars 汽车 ONcars.person_id = t.person_idLEFT JOIN count_children 儿童 ON children.person_id = t.person_idLEFT JOIN count_pets pets ON pets.person_id = t.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)'从人 t左连接(选择 t.person_idCOUNT(*) num_cars从汽车 cGROUP BY t.person_id) 汽车 ONcars.person_id = t.person_id左连接(选择 t.person_idCOUNT(*) num_children从儿童 cGROUP BY t.person_id) children ON children.person_id = t.person_id左连接(选择 p.person_idCOUNT(*) num_pets来自宠物 pGROUP BY p.person_id) pets ON pets.person_id = t.person_id

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?

解决方案

Subquery Factoring (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