MySQL - 计算查询后的行数
我目前在我的网站上运行一个查询，该查询查找没有特定属性的所有行。因此，尽管我有行号的连续数字，但实际结果在主键中会有间隙。事实证明这很难解释，所以我会尝试添加一些代码！
这是我的语句的 WHERE 部分，其余部分并不重要：
...WHERE
thought_deleted = 0
AND
thought_ID <= (SELECT MAX(thought_ID) FROM rmt_thoughts ORDER BY thought_ID) - $start
ORDER BY
thought_date_created DESC
LIMIT $number
$number 和 $start 是使用 PHP 传递给查询的值。我要更改的部分是 AND 部分。我不想查找小于特定 ID 的thought_ID 值，而是想选择好像每个thought_deleted = 0 行都有一个顺序ID。我希望这是有道理的！
我假设我需要a）创建某种别名来计算行数，并按该别名排序，或者b）只做某种限制事情，只查看我想要的值。不管怎样，我已经在谷歌上搜索了一段时间，我已经筋疲力尽，陷入困境。以前有人见过这样的事情吗？
谢谢大家:)
闪闪发光*
I currently have a query running on my site which looks for all rows which don't have a particular attribute. So although I have sequential numbers for the row numbers, the actual result will have gaps in the primary keys. Turns out this is difficult to explain, so I'll try and add some code!
Here's the WHERE part of my statement, the rest is unimportant:
...WHERE
thought_deleted = 0
AND
thought_ID <= (SELECT MAX(thought_ID) FROM rmt_thoughts ORDER BY thought_ID) - $start
ORDER BY
thought_date_created DESC
LIMIT $number
$number and $start are values passed to the query using PHP. The part I want to change is the AND part. Instead of looking for thought_ID values which are less than a particular ID, I want to select as if each thought_deleted = 0 row has a sequential ID. I hope this makes sense!
I'm assuming I need to either a) make some kind of alias which counts the number of rows, and sort by that or b) just do some sort of limit thingy which only looks at the values I want. Either way, I've been googling for a while I am pretty exhausted and stuck. Has anyone seen anything like this before?
Thanks guys :)
Sparkles*
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
显然你想做某种分页。
只需使用
LIMIT、 指定偏移量，而不仅仅是LIMIT。例如，要检索第 1..5 行，请使用
LIMIT 0, 5，对于接下来的 5 行，请使用LIMIT 5, 5。在您的情况下，您可能希望使用
LIMIT $start, $number假设 $start 是从零开始的。Apparently you want to do some kind of pagination.
Simply specify an offset using
LIMIT <offset>, <limit>instead of justLIMIT <limit>.For example, to retrieve rows 1..5 you use
LIMIT 0, 5and for the next 5 rows you useLIMIT 5, 5.In your case you probably want to use
LIMIT $start, $numberassuming $start is zero-based.