SQL查询计数()多个表

发布时间：2020-12-12 08:46:22 所属栏目：MsSql教程来源：网络整理

导读：我有一个表与其他表有几个很多的关系.让我们说主桌是一个人,其他桌子代表宠物,汽车和孩子.我想要一个查询,返回该人的详细信息,宠物,汽车和儿童的数量,例如 Person.Name Count(cars) Count(children) Count(pets)John Smith 3 2 4Bob Brown 1 3 0 这样做最好的

我有一个表与其他表有几个很多的关系.让我们说主桌是一个人,其他桌子代表宠物,汽车和孩子.我想要一个查询,返回该人的详细信息,宠物,汽车和儿童的数量,例如

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

John Smith    3           2               4
Bob Brown     1           3               0

这样做最好的方法是什么？

解决方法

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

使用内联视图：

SELECT t.name,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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!