我有这个表结构：
TABLE: PERSON TABLE: CAR
PersonID PersonID | CarID
------ ---------|---------
1 1 | 51
1 | 52
TABLE: PET TABLE: AGE
PersonID | PetID Person | AgeID
---------|---- -------|----
1 | 81 1 | 20
1 | 82
1 | 81
一个人可以养许多辆汽车和宠物，但只有一个年龄。
我想计算某人拥有的汽车数量，计算某人拥有的宠物数量，并列出其年龄。
这是我到目前为止的内容：
select
car.personid as person,
count(car.carid) as cars,
null as pets
from car
where car.personid = 1
group by car.personid
union all
select
pet.personid as person,
null as cars,
count(pet.petid) as pets
from pet
where pet.personid = 1
group by pet.personid
这将产生：
Person | Cars | Pets
-------|------|-----
1 | 2 | null
1 | null | 3
但我希望结果看起来像这样：
Person | Cars | Pets | Age
-------|------|------|----
1 | 2 | 3 | 20
这里有一个小提琴：http ://sqlfiddle.com/#!3/ f584a/1/0
我完全停留在如何将记录放入一行并添加年龄列的问题上。
查询1：
SELECT p.PersonID,
( SELECT COUNT(1) FROM CAR c WHERE c.PersonID = p.PersonID ) AS Cars,
( SELECT COUNT(1) FROM PET t WHERE t.PersonID = p.PersonID ) AS Pets,
a.AgeID AS Age
FROM PERSON p
LEFT OUTER JOIN
AGE a
ON ( p.PersonID = a.PersonID )
结果：
| PersonID | Cars | Pets | Age |
|----------|------|------|-----|
| 1 | 2 | 3 | 20 |
查询2：
WITH numberOfPets AS (
SELECT PersonID,
COUNT(1) AS numberOfPets
FROM PET
GROUP BY PersonID
),
numberOfCars AS (
SELECT PersonID,
COUNT(1) AS numberOfCars
FROM CAR
GROUP BY PersonID
)
SELECT p.PersonID,
COALESCE( numberOfCars, 0 ) AS Cars,
COALESCE( numberOfPets, 0 ) AS Pets,
AgeID AS Age
FROM PERSON p
LEFT OUTER JOIN AGE a ON ( p.PersonID = a.PersonID )
LEFT OUTER JOIN numberOfPets t ON ( p.PersonID = t.PersonID )
LEFT OUTER JOIN numberOfCars c ON ( p.PersonID = c.PersonID )
结果：
| PersonID | Cars | Pets | Age |
|----------|------|------|-----|
| 1 | 2 | 3 | 20 |
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句