在MySQL (Percona)服务器中有三个表:

我想要做的是找到那些照片赢得最多战争的顶级相册。对于每一张相册,我都想显示一张缩略图,它的特征图片由album_photo.photo识别。
到目前为止我的疑问是:
SELECT
a.id,
a.name,
feature.thumb_path,
SUM(p.wins) wins,
SUM(p.battles) battles,
SUM(p.wins) / SUM(p.battles) album_popularity
FROM
photo p,
album a,
album_photo ap
LEFT JOIN photo feature ON ap.photo_id = feature.id AND ap.featured = 1
WHERE p.id = ap.photo_id AND ap.album_id = a.id
GROUP BY a.id, feature.thumb_path
ORDER BY album_popularity DESC, SUM(p.battles) DESC这个查询按照我所期望的顺序返回结果,但是一些thumb_path值为NULL。每本相册都有一张特色照片。换句话说,album与album_photo是一对多的,而且album_photo中至少有一个行具有featured = 1。
我不是DBA,所以这对你们中的一些人来说是显而易见的,但是.为什么会发生这种事?我觉得我已经有85%的时间了,但是我缺少了一个JOIN,或者用一个新的别名引用了一个已经引用的表,或者某种类型的子查询。也许我得重新加入album_photo和photo?
任何洞察力都将不胜感激。
更新
在采用了Rick建议的语法之后,我得到了以下查询:
SELECT
a.id,
a.name,
ap.photo_id,
p.thumb_path,
SUM(p.wins) wins,
SUM(p.battles) battles,
SUM(p.wins) / SUM(p.battles) album_popularity
FROM album a
JOIN album_photo ap ON a.id = ap.album_id
JOIN photo p ON ap.photo_id = p.id
GROUP BY a.id
HAVING SUM(p.battles) > 1
ORDER BY album_popularity DESC, SUM(p.battles) DESC它填充所有字段,但返回的是相册中的第一张照片(最低ID),而不是特征照片。因此,我想以某种方式将结果集中的每一行链接到album_photo中与每一行的album_id匹配的行,然后再链接回photo。我试过这样做的:
SELECT
a.id,
a.name,
apf.photo_id,
pf.thumb_path,
SUM(p.wins) wins,
SUM(p.battles) battles,
SUM(p.wins) / SUM(p.battles) album_popularity
FROM album a
JOIN album_photo ap ON a.id = ap.album_id
JOIN photo p ON ap.photo_id = p.id
LEFT JOIN album_photo apf ON p.id = apf.photo_id AND apf.featured = 1
LEFT JOIN photo pf ON apf.photo_id = pf.id
GROUP BY a.id
HAVING SUM(p.battles) > 1
ORDER BY album_popularity DESC, SUM(p.battles) DESC这是返回正确的行,但照片信息是NULL,除了相册之外,在相册中,给定相册的特色照片也是id最低的照片。
发布于 2022-01-07 11:32:53
如果数据库无法在照片表中找到匹配的记录,那么只有两种可能性:
如果你说“每一本相册都有一张特色照片。”(我猜他们不是,BTW!)然后,您可以失去左联接,并使用一个常规的,内部连接。
实际上,我建议您在查询中始终使用“内部联接”语法--您目前正在将隐式(“逗号-联接”)和显式连接语法混合在一起,而且很难看出发生了什么。
发布于 2022-01-07 17:12:41
使用JOIN,而不是LEFT JOIN。LEFT is useful when the row in the second table may be missing, and you _want_ NULLs`.
使用现代语法:
FROM a
JOIN b ON b.x = a.y也就是说,使用ON表示表是如何“相关”的;使用WHERE表示“筛选”。
有关多到多表索引的性能指南,请参见此:http://mysql.rjweb.org/doc.php/index_烹饪书_mysql#many_至_许多_映射_表格。
https://dba.stackexchange.com/questions/305752
复制相似问题