sql - 我可以连接两个表，连接表按特定列排序吗？

Question

我不是一个数据库大师，所以我需要一些关于我正在处理的查询的帮助。在我的照片社区项目中，我想通过不仅显示标签名称和计数器(其中的图像数量)来丰富地可视化标签，我还想显示标签内最受欢迎的图像(最多业力)的缩略图。

表格设置如下:

• 图像表保存基本的图像元数据，重要的是业力场
• Imagefile 表包含每个图像的多个条目，每种格式一个条目
• 标签表包含标签定义
• Tag_map 表将标签映射到图像

在我通常的试错查询创作中，我已经走到了这一步:

SELECT * FROM

(SELECT tag.name, tag.id, COUNT(tag_map.tag_id) as cnt
FROM tag INNER JOIN tag_map ON (tag.id = tag_map.tag_id)
INNER JOIN image ON tag_map.image_id = image.id
INNER JOIN imagefile on image.id = imagefile.image_id 
WHERE imagefile.type = 'smallthumb'
GROUP BY tag.name
ORDER BY cnt DESC)

as T1 WHERE cnt > 0 ORDER BY cnt DESC

[为简单起见，删除了内部查询的列子句]

这个查询给了我一些我需要的东西。外部查询确保只返回至少有 1 张图像的标签。内部查询返回标签详细信息，例如其名称、计数(图像数量)和缩略图。此外，我可以根据需要对内部查询进行排序(按大多数图像、字母顺序、最新等)

到目前为止一切顺利。然而，问题在于此查询与标签中最流行的图像(最多业力)不匹配，它似乎总是采用标签中的最新图像。

如何确保最受欢迎的图片与标签匹配？

Answer 1

您正在寻找 'having' 的群组子句，而不是嵌套选择!

SELECT tag.name, tag.id, COUNT(tag_map.tag_id) as cnt
  FROM tag 
 INNER JOIN tag_map 
    ON (tag.id = tag_map.tag_id)
 INNER JOIN image 
    ON tag_map.image_id = image.id
 INNER JOIN imagefile 
    on image.id = imagefile.image_id 
 WHERE imagefile.type = 'smallthumb'
 GROUP BY tag.name HAVING COUNT(tag_map.tag_id) > 0
 ORDER BY cnt DESC