我有3个表。图像，类别，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张图片。最大的图片.id是最新的图片。
例如。如果我有3个类别，每个类别有40张图片。我想展示每个类别中最新的4张图片。稍后，我将尝试显示每个类别的下一个4，然后每个类别的接下来的4，直到没有图像为止。
这个解决方案似乎正是我想要的。
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_。无法弄清楚如何使用额外的表联接来实现这一点。
非常感谢SQL专家的帮助。
您就快到了，只需要使用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;
尽管由于单值规则，这不是严格合法的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意味着每次查看另一组OFFSET时，它都必须重新运行查询。MySQL中有一些优化，一旦找到足够的行，它就会退出查询，但如果你频繁迭代，并深入到一系列页面中，这仍然很昂贵。
您可以使用两种可能的优化：一种是缓存结果的部分，基于这样的理论，即很少有用户希望遍历大分页结果的每一页。例如，获取足够的结果来填充十页的结果，并缓存这些结果。它大大减少了查询次数，也许只有1%的时间用户会进入下一组10页。
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 */
如果您可以假设页面N的任何视图都将来自页面N-1的视图，则另一个优化是请求根据N-1st页面中看到的最大类别id来筛选类别。您需要这样做，因为OFFSET按结果集中的行号工作，但索引偏移按这些行上的值工作。如果可能存在间隙或未使用的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 */
回复您的意见：
使用LIMIT和OFFSET只会修剪这些结果，而不会使我在行列表中下移。
LIMIT按预期工作；它适用于CCD_ 10和CCD_。
在每个类别的最大N查询之前，我的做法是通过
1.拉入x张图片，
2.记住最后一张图片是哪个，然后
3.在我的后续查询中使用子查询来获得下一个x数量的图像，这些图像的id小于上一个图像。每组N最大的情况下，这样的事情可能吗？
这就是我的WHERE子句在上面最后一个例子中所做的，不使用子查询。我假设你正在前进到cat_id的下一个更高的集合。只有当你一次前进一页，并朝着积极的方向前进时，这个解决方案才有效。
好吧，还有另一个适用于greatest-n-per-group的解决方案可以与MySQL一起使用，但它依赖于用户变量功能。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 */
这类似于使用标准SQL和大多数RDBMS支持的ROW_NUMBER() OVER (PARTITION BY cat_id)，但SQLite也不支持。
SELECT *
FROM (
SELECT a.id as ID,a.title as title,b.cat_name as CAT, row_number() OVER (PARTITION BY b.cat_id ORDER BY b.cat_id,a.id desc) AS n
from images a, categories b, image_category c
where a.id = c.image_id
and b.cat_id = c.cat_id
) x
WHERE n < 4
ORDER BY b.cat_id,a.id desc;