代码之家 › 专栏 › 技术社区 › Fer

我可以把两个表连在一起,使连在一起的表按某一列排序吗?

inner-join mysql sql

Fer · 技术社区 · 16 年前

我不是一个数据库专家,所以我需要一些关于我正在处理的查询的帮助。在我的照片社区项目中,我希望通过不仅显示标签名称和计数器(其中的图像),丰富地可视化标签,我还希望显示标签中最流行的图像的拇指(大多数业力)。

表设置如下:

图像表包含基本的图像元数据,重要的是业力场
ImageFile表为每个图像保存多个条目,每个格式一个条目
标记表保存标记定义
标签映射表将标签映射到图像

在我通常的尝试和错误查询创作中,我做到了这一点:

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个图像的标记。内部查询返回标记的详细信息,例如它的名称、计数(图像)和缩略图。此外,我可以根据自己的需要对内部查询进行排序(按大多数图像、字母顺序、最近的图像等)

到现在为止,一直都还不错。然而,问题是这个查询与标签中最流行的图像(最业力)不匹配,它似乎总是取标签中最新的图像。

如何确保最流行的图像与标签匹配?

2 回复 | 直到 16 年前

Community Mohan Dere 9 年前

这应该非常接近:

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'

基本上,这是 the ever-recurring "max-per-group" problem :如何选择与组的最大/最小值相对应的记录?

一般的答案都是这样的:选择你的小组( tag_id, MAX(image.karma) )然后根据这些特性连接基础数据。可能有特定于DBMS的专有扩展采用不同的方法,例如使用 ROW_NUMBER() / PARTITION BY . 但是,这些都不是非常便携的,在使用不支持它们的DBMS时可能会让您抓耳挠腮。

lexu 16 年前

您正在查找分组依据 '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