我可以联接两个表，从而使联接的表按某个列排序吗？

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

这应该非常接近：

SELECT
  tag.id, 
  tag.name,
  tag_group.cnt,
  tag_group.max_karma,
  image.id, 
  imagefile.filename
  /* ... */
FROM
  tag
  /* join against a list of max karma values (per tag) */
  INNER JOIN (
    SELECT   MAX(image.karma) AS max_karma, COUNT(image.*) cnt, tag_map.tag_id
    FROM     image
             INNER JOIN tag_map ON tag_map.image_id = image.id
    GROUP BY tag_map.tag_id
  ) AS tag_group ON tag_group.tag_id = tag.id
  /* join against a list of image ids (per max karma value and tag) */
  INNER JOIN (
    SELECT   MAX(image.id) id, tag_map.tag_id, image.karma
    FROM     image
             INNER JOIN tag_map ON tag_map.image_id = image.id
    GROUP BY tag_map.tag_id, image.karma /* collapse >1 imgs with same karma */
  ) AS pop_img ON pop_img.tag_id = tag.id AND pop_img.karma = tag_group.max_karma
  /* join against actual base data (per popular image id) */
  INNER JOIN 
    image ON image.id = pop_img.id
  INNER JOIN
    imagefile ON imagefile.image_id = pop_img.id AND imagefile.type = 'smallthumb'

基本上，这是一个经常出现的“每个组的最大值”问题：如何选择与一个组的最大值/最小值相对应的记录？

通常的答案通常是：选择组（tag_id, MAX(image.karma)），然后根据这些特征将基础数据结合起来。可能存在特定于DBMS的专有扩展，它们采用了不同的方法，例如，使用ROW_NUMBER()/PARTITION BY。但是，它们不是很方便携带，在使用不支持它们的DBMS时可能会挠头。