php - 具有关系表连接的 SQL 每个组最大 n

Question

我有 3 张 table 。图片，类别，image_category。

image:         id | title | imageURL
categories:     cat_id | cat_name
image_category: image_id | cat_id

我当前按最新到最旧的顺序选择所有图像的查询是:

SELECT image.id as ID, image.title as title, categories.cat_name as CAT 
FROM image_category 
LEFT JOIN image 
ON image_category.image_id = image.id 
INNER JOIN categories 
ON image_category.cat_id = categories.cat_id 
ORDER BY ID DESC

我想为每个类别显示最新的 4 张图片。最大的 image.id 是最新的图像。

例如。如果我有 3 个类别，每个类别有 40 张图像。我想展示每个类别的最新 4 张图片。之后我将尝试显示每个类别的下一个 4 个，然后每个类别显示下一个 4 个，直到没有图像为止。

This解决方案似乎是我正在寻找的。

SELECT i1.*
FROM item i1
LEFT OUTER JOIN item i2
ON (i1.category_id = i2.category_id AND i1.item_id < i2.item_id)
GROUP BY i1.item_id
HAVING COUNT(*) < 4
ORDER BY category_id, date_listed;

但我有一个连接我的 image_id 和 category_id 的关系表。无法弄清楚如何使用额外的表连接来实现这一点。

希望得到 SQL 大师的帮助。

Answer 1

你快到了，你只需要使用你的 item_category 进行分组。表，因为那是 cat_id 所在的位置。

SELECT ...
FROM item_category AS c1
LEFT OUTER JOIN item_category AS c2
  ON c1.cat_id = c2.cat_id AND c1.image_id < c2.image_id
GROUP BY c1.cat_id
HAVING COUNT(*) < 4

那么一旦你知道了，你就会知道 c1包含每个类别的前四张图像。然后您可以加入 c1到 image获取其他属性的表:

SELECT i.id, i.title, c.cat_name AS CAT 
FROM item_category AS c1
LEFT OUTER JOIN item_category AS c2
  ON c1.cat_id = c2.cat_id AND c1.image_id < c2.image_id
INNER JOIN image AS on c1.image_id = i.id
INNER JOIN categories AS c on c1.cat_id = c.id
GROUP BY c1.image_id
HAVING COUNT(*) < 4;

尽管由于 single-value rule 这不是严格合法的 SQL , MySQL 将允许它。

从评论线程复制:

我会获取完整的结果，将其存储在缓存中，然后使用应用程序代码按我的意愿对其进行迭代。那将更简单并且具有更好的性能。 SQL 功能强大，但另一种解决方案可能更易于开发、调试和维护。

您当然可以使用 LIMIT遍历结果集:

SELECT i.id, i.title, c.cat_name AS CAT 
FROM item_category AS c1
LEFT OUTER JOIN item_category AS c2
  ON c1.cat_id = c2.cat_id AND c1.image_id < c2.image_id
INNER JOIN image AS on c1.image_id = i.id
INNER JOIN categories AS c on c1.cat_id = c.id
GROUP BY c1.image_id
HAVING COUNT(*) < 4
ORDER BY c.cat_id
LIMIT 4 OFFSET 16;

但请记住，执行 OFFSET 意味着每次查看另一组查询时都必须重新运行查询。 MySQL 中有一些优化，因此一旦找到足够的行，它就会退出查询，但如果您频繁迭代，并且深入到一系列页面，它仍然很昂贵。

您可以使用两种可能的优化:一种是缓存部分结果，理论上很少有用户想要浏览大型分页结果的每一页。因此，例如，获取足以填充十页结果的内容，并将其缓存。它大大减少了查询的数量，也许只有 1% 的时间用户会进入下一组十页。

SELECT i.id, i.title, c.cat_name AS CAT 
FROM item_category AS c1
LEFT OUTER JOIN item_category AS c2
  ON c1.cat_id = c2.cat_id AND c1.image_id < c2.image_id
INNER JOIN image AS on c1.image_id = i.id
INNER JOIN categories AS c on c1.cat_id = c.id
GROUP BY c1.image_id
HAVING COUNT(*) < 4
ORDER BY c.cat_id
LIMIT 40 OFFSET 40; /* second set of ten pages */

另一个优化，如果您可以假设页面的任何 View N将来自页面 N-1 的 View , 用于根据 N-1 中看到的最大类别 ID 过滤类别的请求。圣页。您需要这样做，因为 OF​​FSET 按结果集中的行号工作，但索引偏移按在这些行上找到的值工作。如果可能存在间隙或未使用的 cat_id 值，则这些偏移量不同。

SELECT i.id, i.title, c.cat_name AS CAT 
FROM item_category AS c1
LEFT OUTER JOIN item_category AS c2
  ON c1.cat_id = c2.cat_id AND c1.image_id < c2.image_id
INNER JOIN image AS on c1.image_id = i.id
INNER JOIN categories AS c on c1.cat_id = c.id
WHERE c1.cat_id > 47 /* this value is the largest seen in previous page */ 
GROUP BY c1.image_id
HAVING COUNT(*) < 4
ORDER BY c.cat_id
LIMIT 40; /* no offset needed */

回复您的评论:

... using LIMIT and OFFSET will only trim those results and not move me down the list of rows.

LIMIT正在按预期工作；它适用于 GROUP BY 之后的结果行和 HAVING已经完成了他们的工作。

The way I was doing it before the greatest N per category query is by
1. pulling in x amount of images,
2. Remembering which was the last image, and then
3. using a sub query on my subsequent queries to get the next x amount of images with ids smaller than than the last image. Is something like that possible with greatest N per group?

这就是我的 WHERE子句在上面的最后一个示例中没有使用子查询。而且我假设您正在进入下一个更高的 cat_id 集。此解决方案仅在您一次前进一页并且朝正方向前进时才有效。

好吧，还有另一种适用于 MySQL 的每个组最大 n 的解决方案，但它依赖于用户变量功能。 SQLite 没有这个特性。

SELECT * FROM (
  SELECT 
    p.id as image_ID, p.imageURL as URL, c.cat_name as CAT, ic.cat_id,
    IF(@cat=ic.cat_id, @row:=@row+1, @row:=1) AS _row, @cat:=ic.cat_id AS _cat
  FROM (SELECT @cat:=null, @row:=0) AS _init
  CROSS JOIN image_category AS ic
  INNER JOIN portfolio AS p ON ic.image_id = p.id
  INNER JOIN categories AS c on ic.cat_id = c.cat_id
  ORDER BY ic.cat_id, ic.image_id
) AS x
WHERE _row BETWEEN 4 AND 6;  /* or choose any range you want */

这类似于使用 ROW_NUMBER() OVER (PARTITION BY cat_id)标准 SQL 和大多数 RDBMS 都支持，但 SQLite 还不支持。