我的例子有点愚蠢,但这是我想到的最好的;)
Zoo
===
Id
Name
Animal
=======
Id
Species
ZooId
Zookeeper
=========
Id
FullName
ZooId
在那里有以下行:
Zoo
===
1, New York
2, Berlin
Animal
======
1, Tiger, 1
2, Monkey, 1
3, Bear, 1
4, Tiger, 2
5, Zebra, 2
Zookeeper
=========
1, John Doe, 1
2, Peter Smith, 2
3, Ronald Jackson, 2
4, Michael Miller, 2
我的期望输出应该是:
New York, 3, 1
Berlin, 2, 3
我尝试了下面的查询,但它是无意义的。
SELECT Zoo.Name, COUNT(Animal.Id) AS Animals, COUNT(Zookeeper.Id) AS Zookeepers
FROM Zoo
LEFT JOIN Animal ON Animal.ZooId = Zoo.Id
LEFT JOIN Zookeper ON Zookeeper.ZooId = Zoo.Id
GROUP BY Zoo.Name
两列的计数相同,即使是小数据集也需要几分钟。
Try
SELECT z.Name,
COALESCE(a.Animals, 0) Animals,
COALESCE(k.Zookeepers, 0) Zookeepers
FROM Zoo z LEFT JOIN
(
SELECT zooid, COUNT(*) Animals
FROM animal
GROUP BY zooid
) a ON z.id = a.zooid LEFT JOIN
(
SELECT zooid, COUNT(*) Zookeepers
FROM zookeeper
GROUP BY zooid
) k ON z.id = k.zooid
输出:| NAME | ANIMALS | ZOOKEEPERS |
------------------------------------
| New York | 3 | 1 |
| Berlin | 2 | 3 |
这里是SQLFiddle
试试这个,
select t1.nm,t2.Animal_count,t3.Zookeeper_count
FROM Table1 t1
INNER JOIN
(select t22,count(Animal) AS Animal_count from Table2 group by(t22)) t2
ON t1.id=t2.t22
INNER JOIN
(select t32,count(name) As Zookeeper_count from Table3 group by(t32)) t3
ON t1.id=t3.t32
SQLFiddle演示