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