一点点复杂的sql行位置 [英] little complex sql row postion

问题描述

基本上我有相册，其中包含50张图像..如果现在我显示图像列表，我知道从哪一行显示(显示:20到30，共50张)，表示从20到30显示10行现在好了，问题是，我想选择一个图像，但仍然显示选择了哪个位置，因此我可以来回移动，但也要保持该位置.

basically i have albums, which has 50 images init.. now if i show list of images, i know from which to which row is showing (showing: 20 to 30 of 50), means showing 10 rows from 20 - 30. well now the problem is, i want to select an image, but still show which postion was it selected, so i can move back and forth, but keep the postion too.

就像我选择ID为'sd564'的第5张图像一样，我要显示(50张图像中的6张)，意味着您看到的是50张图像中的6张.我想展示(50张图片中的7张).

like if i select 5th image, which id is 'sd564', i want to show (6 of 50 images), means you are seeing 6th of 50 images.. if i get next row id and show that, then, i want to show (7 of 50 images).

好吧，我可以通过分页指针轻松完成所有这些操作，例如在url中说(after = 5，after = 6)...它随位置移动，但是如果我没有这个(after = 6)而只有一个ID，我该怎么办?

well i can do all this from pagination pointer easily, like in url say (after=5, after=6)... its moving with postion, but what if i dont have this (after=6) and just have an id, how can i still do that?

我也不想使用(after = 6)，因为它的动态站点和图像会添加和删除，因此定位chnages并与其他人共享并返回到相同的旧链接，那么它将是错误的位置.

i dont want to use (after=6) also because its dynamic site and images adds and delete, so position chnages and sharing with someone else and going back on same old link, then it would be wrong position.

我应该为此运行哪种类型的sql查询?

what kind of sql query should i be running for this?

我现在有

select * from images where id = 'sd564'; 

很明显，我需要在查询中添加限制或其他内容以获取我想要的内容，或者运行另一个查询以获取结果，同时也要保留此旧查询.无论如何，我只想定位.我希望你能帮助我解决这个问题

obviously i need to add limit or some other thing in query to get what i want or maybe run another query to get the result, while keeping this old query inplace too. anyway i just want positioning. i hope you can help me solve this

示例: http://media.photobucket.com/image/color%20splash/aly3265/converse.jpg

示例http://img41.imageshack.us/img41/5631/viewing3of8240. png

相册查询请求(请在下面查看帖子)

Album Query Request (check post below)

select images.* from images, album
where album_id = '5'
and album_id = image_album_id
order by created_date DESC
limit ....;

推荐答案

假定created_date对于每个album_id是唯一的，并且(album_id，created_date)对于images中的所有行都是唯一的，那么:

Assuming created_date is unique per album_id and (album_id,created_date) is unique for all rows in images, then this:

select     i1.*, count(*) as position
from       images i1
inner join images i2
on         i1.album_id      = i2.album_id     -- get all other pics in this album
and        i1.created_date >= i2.created_date -- in case they were created before this pic
where      i1.album_id = 5
group by   i1.created_date

将可靠地为您获取图像及其位置.请理解，这仅在(album_id，created_date)在整个图像表中是唯一的情况下才能可靠地工作.如果不是这种情况，则位置将不可靠，并且由于GROUP BY的原因，您可能看不到所有照片.还要注意，像这样的GROUP BY子句，仅列出出现在SELECT列表中的某些列(在本例中为images.*)在大多数RDBMS-es中无效.有关此问题的详细讨论，请参见: http: //dev.mysql.com/tech-resources/articles/debunking-group-by-myths.html

will reliably get you the images and their position. Please understand that this will only work reliably in case (album_id,created_date) are unique throughout the images table. If that is not the case, the position wont be reliable, and you might not see all photos due to the GROUP BY. Also note that a GROUP BY clause like this, only listing some of the columns that appear in the SELECT list (in this case images.*) is not valid in most RDBMS-es. For a detailed discussion on that matter, see: http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths.html

这样做:

select     i1.*, count(*) as position
from       images i1
inner join images i2
on         i1.album_id      = i2.album_id     -- get all other pics in this album
and        i1.created_date >= i2.created_date -- in case they were created before this pic
where      i1.album_id = 5
group by   i1.created_date
having     count(*) = 4

您选择第4个位置的图像(请注意having count(*) = 4)

you select the image at the 4th position (note the having count(*) = 4)

这样做:

select     i1.*, count(*) as position
from       images i1
inner join images i2
on         i1.album_id      = i2.album_id     -- get all other pics in this album
and        i1.created_date >= i2.created_date -- in case they were created before this pic
where      i1.album_id = 5
group by   i1.created_date
having     count(*) between 1 and 10

您选择位置1到10的所有照片(再次注意having子句.)

you select all photos with positions 1 through 10 (note the having clause again.)

当然，如果您只想要一张特定的图像，则只需执行以下操作即可:

Of course, if you just want one particular image, you can simply do:

select     i1.*, count(*) as position
from       images i1
inner join images i2
on         i1.album_id      = i2.album_id     -- get all other pics in this album
and        i1.created_date >= i2.created_date -- in case they were created before this pic
where      i1.image_id = 's1234' 
group by   i1.created_date

这将正确报告图像在相册中的位置(当然，假设image_id在images表中是唯一的).在这种情况下，您不需要Have子句，因为您已经精确定位了想要的图像.

This will correctly report the position of the image within the album (of course, assuming that image_id is unique with in the images table). You don't need the having clause in that case since you already pinpointed the image you want.

这篇关于一点点复杂的sql行位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！