MYSQL - 限制在一个范围内
我已经使用 AJAX 构建了一个滑块来获取结果，但我遇到了一个小问题，如下所示：
假设一个表包含一组 1 到 15 行，但是，我只处理前 9 行行；在第一阶段 i (SELECT * FROM table ORDER BY id DESC LIMIT 9) 并使用结果构建导航并显示第一组。
然后在第二阶段我正在查询这9个项目，每次从左侧开始查询3个，所以我(SELECT * FROM table WHERE id > '$1' ORDER BY id DESC LIMIT 3) 或 right 所以我 (SELECT * FROM table WHERE id < '$1' ORDER BY id DESC LIMIT 3) 其中 $1 是每组的最后一项，因此 (13 || 11 || 7) 来自 (15, 14, 13 ) || (12, 11, 10) || (9,8,7)。
好吧，想象一下上面的详细解释的表示，如下所示：
<- [ 15 - 14 - 13 - 12 - 11 - 10 - 9 - 8 - 7 ] ->
[ 15 ] [ 14 ] [ 13 ]
好的，问题是当“右查询”到达最后一项：7时，结果 - set 是 (6, 5, 4) 显然是正确的，但不在 我们的 15 到 7 范围内，相反，出于我的目的，它应该不返回任何内容。我怎样才能用一个 SQL 查询来做到这一点？
只是为了方便，这里是滑块演示（右侧的最新项目，使用向右箭头，超出范围）； 希望这不是一个太模糊的问题，因为我很难弄清楚如何解释这个问题。 ;)
I have built a slider using AJAX for the results, and i have a little problem as follow:
assuming a table with a set of 1 to 15 rows, but, i'm working only with the first 9 rows; in a 1st stage i (SELECT * FROM table ORDER BY id DESC LIMIT 9) and use the results to build navigation and for displaying the first group.
Then in a 2nd stage i'm querying theese 9 items, 3 each time from left so i (SELECT * FROM table WHERE id > '$1' ORDER BY id DESC LIMIT 3) or right so i (SELECT * FROM table WHERE id < '$1' ORDER BY id DESC LIMIT 3) where $1 is the last item of each group so (13 || 11 || 7) from (15, 14, 13) || (12, 11, 10) || (9, 8, 7).
Well, immagine to have a representation of above verbose explanation like below:
<- [ 15 - 14 - 13 - 12 - 11 - 10 - 9 - 8 - 7 ] ->
[ 15 ] [ 14 ] [ 13 ]
Ok, the problem is when the "right-query" reach the last item: 7, the results-set is (6, 5, 4) that obviously is correct but isn't in our range of 15 to 7, instead for my purpose it should return nothing. how i can do this with one SQL query?
just for sake here is the slider demo ( latest item to the right, using the right arrow, is out of range );
hope it is not too fuzzy question cause i had an hard time figuring out how to explain the problem. ;)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
在第一阶段，您可以记录返回行中的最小id。然后在您的其他查询中，只需添加一个 WHERE 条件以确保所选 id 大于或等于该最小值，例如，
在您的最小 id 为 7 并且“正确”查询使用值 7 完成的情况下，它不会返回任何内容。如果我正确理解你的问题，这就是你想要的行为。
In the 1st stage, you can record the minimum id in the returned rows. Then in your other queries, just add a WHERE condition to make sure the selected ids are greater than or equal to that minimum, for example
in the case where your minimum id is 7 and the "right" query is done with the value 7, it will return nothing. If I understand your question correctly, this is the behavior you want.