小编典典

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

sql

我不是数据库专家,所以我需要一些有关正在处理的查询的帮助。在我的照片社区项目中,我不仅要显示标签名称和计数器(标签中的图像数量),而且还想显示标签中最受欢迎的图像(最因果),以丰富标签的可视化效果。

表设置如下:

  • 图像表包含基本图像元数据,重要的是因果字段
  • 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张图片的标签。内部查询返回标签详细信息,例如其名称,计数(图像数量)和拇指。另外,我可以根据需要对内部查询进行排序(按大多数图像,按字母顺序,最新等)

到目前为止,一切都很好。但是,问题在于此查询与标签中最流行的图像(大多数业力)不匹配,它似乎总是采用标签中的最新图像。

如何确定最受欢迎的图片与标记匹配?


阅读 420

收藏
2021-03-08

共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时可能会挠头。

2021-03-08