我可以连接两个表，其中连接的表按特定列排序吗? [英] Can I join two tables whereby the joined table is sorted by a certain column?

问题描述

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

表格设置如下:

• Image 表包含基本的图像元数据，重要的是 karma 字段
• Imagefile 表为每个图像保存多个条目，每个格式对应一个条目
• 标签表包含标签定义
• Tag_map 表将标签映射到图像

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

SELECT * FROM(SELECT tag.name, tag.id, COUNT(tag_map.tag_id) 作为 cntFROM tag INNER JOIN tag_map ON (tag.id = tag_map.tag_id)内连接图像上 tag_map.image_id = image.idimage.id = imagefile.image_id 上的 INNER JOIN imagefileWHERE imagefile.type = 'smallthumb'按标签名分组按 cnt DESC 排序)作为 T1 WHERE cnt >0 按 cnt DESC 排序

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

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

到目前为止一切顺利.然而，问题是该查询与标签中最受欢迎的图像(最大业力)不匹配，它似乎总是采用标签中的最新图像.

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

解决方案

这应该很接近了:

SELECT标签.id,标签名，tag_group.cnt,tag_group.max_karma,图像编号，图像文件.文件名/* ... */从标签/* 加入最大 karma 值列表(每个标签)*/内部联接 (SELECT MAX(image.karma) AS max_karma, COUNT(image.*) cnt, tag_map.tag_id从图片中INNER JOIN tag_map ON tag_map.image_id = image.idGROUP BY tag_map.tag_id) AS tag_group ON tag_group.tag_id = tag.id/* 加入图像 id 列表(每个最大 karma 值和标签)*/内部联接 (SELECT MAX(image.id) id, tag_map.tag_id, image.karma从图片中INNER JOIN tag_map ON tag_map.image_id = image.idGROUP BY tag_map.tag_id, image.karma/* 折叠 >1 具有相同 karma 的 imgs */) AS pop_img ON pop_img.tag_id = tag.id AND pop_img.karma = tag_group.max_karma/* 加入实际基础数据(每个流行的图像 ID)*/内部联接image ON image.id = pop_img.id内部联接imagefile ON imagefile.image_id = pop_img.id AND imagefile.type = 'smallthumb'

基本上，这是反复出现的max-per-group"问题:如何才能我选择与组的最大值/最小值相对应的记录?

一般的答案始终是:选择您的组(tag_id, MAX(image.karma))，然后根据这些特征加入您的基本数据.可能存在采用不同方法的特定于 DBMS 的专有扩展，例如使用 ROW_NUMBER()/PARTITION BY.然而，这些不是很便携，当您使用不支持它们的 DBMS 时，可能会让您摸不着头脑.

I'm not much of a database guru so I need some help on a query I'm working on. In my photo community project I want to richly visualize tags by not only showing the tag name and counter (# of images inside them), I also want to show a thumb of the most popular image inside the tag (most karma).

The table setup is as follow:

• Image table holds basic image metadata, important is the karma field
• Imagefile table holds multiple entries per image, one for each format
• Tag table holds tag definitions
• Tag_map table maps tags to images

In my usual trial and error query authoring I have come this far:

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

[column clause of inner query snipped for the sake of simplicity]

This query gives me somewhat what I need. The outer query makes sure that only tags are returned for which there is at least 1 image. The inner query returns the tag details, such as its name, count (# of images) and the thumb. In addition, I can sort the inner query as I want (by most images, alphabetically, most recent, etc)

So far so good. The problem however is that this query does not match the most popular image (most karma) of the tag, it seems to always take the most recent one in the tag.

How can I make sure that the most popular image is matched with the tag?

解决方案

This should be pretty close:

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'

Basically, this is the ever-recurring "max-per-group" problem: How can I select the record that corresponds to the maximum/minimum value of a group?

And the general answer always is along the lines of: Select your group (tag_id, MAX(image.karma)) and then join your base data against these characteristics. There may be DBMS-specific proprietary extensions that take a different approach, for example using ROW_NUMBER()/PARTITION BY. However, these are not very portable and may leave you scratching your head when working with a DBMS that does not support them.

这篇关于我可以连接两个表，其中连接的表按特定列排序吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！