本文介绍了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
这篇关于SQL Query to Count() 多个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!
本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!