如何使用左侧连接mysql从另一个表派生count(*) ?

I have table definition like below:

我有如下表定义:

Place
(id, name)
地方(id、名称)
Review
(id, userid, placeid)
评估(id、用户id placeid)
Favorite
(id, userid, placeid)
最喜欢的(id、用户id placeid)
Photo
(id, url, placeid)
where placeid is foreign key to the id of Place table.
图(id, url, placeid)， placeid是Place表id的外键。

On that table, I want to derive this kind of information:
- placeid, place name, totalReview, totalFavorite, totalPhoto.

在这张表上，我想要得到这样的信息:- placeid, place name, totalReview, totalFavorite, totalPhoto。

I got stucked. My progress currently I can derive information just from 1 table, like I can know totalReview of place, by using this mysql statement: SELECT p.*, count(r.id) as totalReview from Place p left join Review r on p.id = r.placeid group by p.id. But, I don't know how I can derive the totalFavorite and totalPhoto.

我被卡住了。我目前的进展可以从1个表中获得信息，就像我可以通过这个mysql语句:选择p。*，计数(r.id)，作为totalReview，从Place p left join Review r on p。id = r。placeid p.id集团。但是，我不知道怎样才能得出totalFavorite和totalPhoto。

2 个解决方案

#1

You need to aggregate each table separately. Here is one solution:

您需要分别聚合每个表。这里有一个解决方案:

SELECT p.*, 
       totalreview, 
       totalfavorite, 
       totalphoto 
FROM   place p 
       LEFT OUTER JOIN (SELECT placeid, 
                               Count(*) AS totalReview 
                        FROM   review 
                        GROUP  BY placeid) r 
                    ON p.placeid = r.placeid 
       LEFT OUTER JOIN (SELECT placeid, 
                               Count(*) AS totalFavorite 
                        FROM   favorite 
                        GROUP  BY placeid) f 
                    ON p.placeid = f.placeid 
       LEFT OUTER JOIN (SELECT placeid, 
                               Count(*) AS totalPhoto 
                        FROM   photo 
                        GROUP  BY placeid) ph 
                    ON p.placeid = ph.placeid

#2

This is a simple way to do this:

这是一个简单的方法:

SELECT
p.id, p.name,
(SELECT COUNT(*) FROM Review r WHERE r.placeId=p.id) AS totalReview
(SELECT COUNT(*) FROM Favorite f WHERE f.placeId=p.id) AS totalFavorite
(SELECT COUNT(*) FROM Photo ph WHERE ph.placeId=p.id) AS totalPhoto
FROM Place p

#1