我如何使用 LEFT JOIN mysql 从另一个表中导出 count(*)

问题描述

我有如下表定义:

• 地点
  (id，名称)
• 评论
  (id、userid、placeid)
• 收藏夹
  (id、userid、placeid)
• 照片
  (id、url、placeid)
  其中 placeid 是 Place 表 id 的外键.

• Place
  (id, name)
• Review
  (id, userid, placeid)
• Favorite
  (id, userid, placeid)
• Photo
  (id, url, placeid)
  where placeid is foreign key to the id of Place table.

在那张桌子上，我想导出这样的信息:
- placeid、地名、totalReview、totalFavorite、totalPhoto.

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

我被卡住了.我的进度目前我只能从 1 个表中获取信息，就像我可以通过使用这个 mysql 语句知道地方的 totalReview 一样:SELECT p.*, count(r.id) as totalReview from Place p left join Review r on p.id = r.placeid group by p.id.但是，我不知道如何推导出 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.

推荐答案

您需要分别聚合每个表.这是一种解决方案:

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

这篇关于我如何使用 LEFT JOIN mysql 从另一个表中导出 count(*)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！