MYSQL为最后5个不同的记录选择5个记录 [英] MYSQL Select 5 records for the last 5 distinct records
问题描述
我在这里搜索了几种不同的方法来执行此操作，但是还不能完全正常工作.基本上，我有一张桌子，上面记录着添加到网站上的图像.每个图像都放在此表中.我想从每个不同的已添加"字段中获取前5张图像.
I've searched on here for a few different ways to do this, but can't quite get this to work. Basically, I have a table with a record of images added to a website. Each image is put into this table. I want to grab the first 5 images from each distinct Added field.
因此，表可能看起来像这样:
So, the table may look like this:
ID File Folder Added
----------------------------------
13 13.jpg Event3 20130830
12 12.jpg Event3 20130830
11 11.jpg Event3 20130830
10 10.jpg Event3 20130830
9 9.jpg Event3 20130830
8 8.jpg Event2 20130701
7 7.jpg Event2 20130701
6 6.jpg Event2 20130701
5 5.jpg Event2 20130701
4 4.jpg Event1 20130615
3 3.jpg Event1 20130615
2 2.jpg Event1 20130615
1 1.jpg Event1 20130615
我希望退货是这样的:
ID File Folder Added
----------------------------------
13 13.jpg Event3 20130830
12 12.jpg Event3 20130830
8 8.jpg Event2 20130701
7 7.jpg Event2 20130701
4 4.jpg Event1 20130615
3 3.jpg Event1 20130615
因此，基本上可以获得最后5个不同的添加"日期的最后5张图像(按照ID最高的顺序排序)(再次通过添加"字段对最新日期进行排序).谢谢！
So basically getting the last 5 (sorted by highest ID first) images for the last 5 distinct 'Added' dates (again sorted for the most recent ones by the Added field). Thank you!
编辑---------------
因此，它更清晰了……我有一张桌子，上面有一张我已上传到基于图像的网站的图像.对于网站的前端，我希望有一个新闻摘要，显示最近上传了图像的5个画廊，并显示每个画廊的5个图像. mysql中的每个图像都有一个上载日期(添加")，该日期与图库相对应，因为我一次只将图像上载到一个图库中，并且ID编号随添加的每个图像自动增加("ID") .当然还有很多其他领域，但是对于我想做的事情来说，那些是最重要的.
So it's a bit more clear... I have a table full of images I've uploaded to an image based website. For the front of the website, I want to have a news blurb that shows the last 5 galleries that have had images uploaded to them, and display 5 images from each of those galleries. Each image that is in the mysql has an uploaded date ('Added') which corresponds to the gallery since I only upload images to a gallery one day at a time , and ID Number that auto increases with every image added ('ID'). There's a bunch of other fields of course, but those are the most important ones for what I'm trying to do.
编辑#2 ----------
我对此措辞有很多困惑，很难解释，但是基本上我如何从表中获得5个不同的添加日期字段的5个最高ID字段?
There's a lot of confusion over how I'm wording this, it's a bit tough to explain but basically how can I get 5 of the highest ID fields for 5 distinct Added date fields from a table?
我相信它与此有关，但是在我运行它时却不起作用:
I believe it has to do with this but it does not work when I run it:
select TOP 5 * from (
select *,
row_no = row_number() over (partition by Added order by ID)
from AviationImages) d
where d.row_n <= 5
推荐答案
在许多其他DBMS(Oracle，SQL-Server，Postgres)中，您可以使用窗口函数:
In many other DBMS (Oracle, SQL-Server, Postgres) you could use window functions:
SELECT id, file, folder, added
FROM
( SELECT id, file, folder, added,
DENSE_RANK() OVER (ORDER BY added DESC) AS d_rank,
ROW_NUMBER() OVER (PARTITION BY added ORDER BY id DESC) AS row_no
FROM AviationImages
) d
WHERE d_rank <= 5 -- limit number of dates
AND row_no <= 5 ; -- limit number of images per date
在MySQL中，您没有窗口函数和OVER子句的奢侈:
In MySQL you don't have the luxury of window function and OVER clause:
SELECT i.id, i.file, i.folder, i.added
FROM
( SELECT DISTINCT added
FROM AviationImages
ORDER BY added DESC
LIMIT 5
) AS da
JOIN
AviationImages AS i
ON i.added = da.added
AND i.id >= COALESCE(
( SELECT ti.id
FROM AviationImages AS ti
WHERE ti.added = da.added
ORDER BY ti.id DESC
LIMIT 1 OFFSET 4
), -2147483647) ; -- use 0 if the `id` is unsigned int
(added, id)上的索引将提高效率-如果表使用InnoDB并且id是主键，则仅(added)上的索引就足够了.
An index on (added, id) will help efficiency - and if the table uses InnoDB and the id is the primary key, then just an index on (added) will be enough.
这篇关于MYSQL为最后5个不同的记录选择5个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
