MySQL MySql查询，选择大于
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/360961/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySql Query, Select greater than
提问by Electronic Zebra
I've got a table, called faq_questions with the following structure:
我有一个名为 faq_questions 的表，其结构如下：
id int not_null auto_increment,
question varchar(255),
sort_order int
I'm attempting to build a query that given a sort order, selects the row with the next highest sort order.
我正在尝试构建一个给定排序顺序的查询，选择具有下一个最高排序顺序的行。
Example:
例子：
id question sort_order
1 'This is question 1' 10
2 'This is question 2' 9
3 'This is another' 8
4 'This is another one' 5
5 'This is yet another' 4
Ok, so imagine I pass in 5 for my known sort order (id 4), I need it to return the row with id 3. Since there's no guarantee that sort_order will be contiguous I can't just select known_sort_order + 1.
好的，假设我为已知的排序顺序 (id 4) 传入 5，我需要它返回 ID 为 3 的行。由于不能保证 sort_order 是连续的，因此我不能只选择 known_sort_order + 1。
Thanks!
谢谢！
回答by Eran Galperin
It seems too simple, but it looks like what you need:
看起来太简单了，但它看起来像你需要的：
SELECT id,question FROM `questions`
WHERE `sort_order` > sort_order_variable
ORDER BY sort_order ASC
LIMIT 1
回答by Alexei Tenitski
SELECT * FROM table_name WHERE sort_order > 5 ORDER BY sort_order ASC LIMIT 1
回答by dkretz
You can do it with TOPor LIMIT:
你可以用TOP或来做到LIMIT：
SELECT TOP 1 * FROM faq_questions
WHERE sort_order > 5
ORDER BY sort_order ASC
but that's not as elegant or portable as
但这没有那么优雅或便携
SELECT *
FROM faq_questions AS f1
LEFT JOIN faq_questions AS f2
ON f1.sort_order > f2.sort_order
AND f2.sort_order = 5
LEFT JOIN faq_questions AS f3
ON f3.sort_order BETWEEN f1.sort_order AND f2.sort_order
WHERE f3.id IS NULL
回答by Electronic Zebra
SELECT
id, question, sort_order
FROM faq_questions
WHERE sort_order in
(SELECT
MIN(sort_order)
FROM faq_questions
WHERE sort_order > ?);
That seems to work
这似乎有效
