sql - 具有两个连接的聚合 (MySQL)

Question

我有一张名为画廊的 table 。对于 gallery 中的每一行，表 picture 中有几行。一张照片属于一个画廊。然后是表格vote。每一行都是对某个画廊的赞成票或反对票。这是(简化的)结构:

gallery ( gallery_id )
picture ( picture_id, picture_gallery_ref )
vote ( vote_id, vote_value, vote_gallery_ref )

现在我想要一个查询为我提供以下信息:所有画廊都有自己的数据字段和连接到画廊的图片数量以及投票的汇总值。

这是我的查询，但由于多重连接，聚合值不正确。 (至少当图片或投票超过一行时。)

SELECT 
  *, SUM( vote_value ) as score, COUNT( picture_id ) AS pictures
FROM 
  gallery
LEFT JOIN 
  vote
  ON gallery_id = vote_gallery_ref
LEFT JOIN 
  picture
  ON gallery_id = picture_gallery_ref
GROUP BY gallery_id

因为我注意到 COUNT( DISTINCT picture_id ) 给了我正确数量的图片，所以我尝试了这个:

( SUM( vote_value ) / GREATEST( COUNT( DISTINCT picture_id ), 1 ) ) AS score

它在本例中有效，但如果一个查询中有多个连接怎么办？

只是想知道是否有更好或更“优雅”的方法可以解决这个问题。另外我想知道我的解决方案是特定于 MySQL 还是标准 SQL？

Answer 1

Okham 的 William 的这句话适用于此:

Enita non sunt multiplicanda praeter necessitatem

(Latin for "entities are not to be multiplied beyond necessity").

您应该重新考虑为什么需要在单个查询中完成此操作？的确，单个查询的开销比多个查询的开销要少，但如果单个查询的性质变得太复杂，既供您开发，也供 RDBMS 执行，然后运行单独的查询。