I have a table with N rows, and I wanna select N-1 rows.
我有一个N行的表，我想选择N-1行。
Suggestions on how to do this in one query, if it's possible..?
如果有可能，建议如何在一个查询中执行此操作..？
4 个解决方案
#1
15
Does the last row have the highest ID? If so, I think this would work:
最后一行的ID最高吗？如果是这样，我认为这会奏效：
SELECT * FROM TABLE WHERE ID != (SELECT MAX(ID) FROM TABLE)
MySQL does allow subselects in the current version, right?
MySQL确实允许当前版本中的子选择，对吗？
However, in most cases, it'd probably perform better if you selected all the rows and then filtered the unwanted data out in your application.
但是，在大多数情况下，如果您选择了所有行，然后在应用程序中过滤掉不需要的数据，它可能会表现得更好。
#2
2
SELECT DISTINCT t1.columns FROM table t1
INNER JOIN table t2 ON t1.id < t2.id
SELECT DISTINCT t1.columns FROM table t1 INNER JOIN table t2 ON t1.id
In my experience, MySQL loves this technique, going back several versions.
根据我的经验，MySQL喜欢这种技术，可以追溯到几个版本。
#3
1
Another technique I don't see listed here is
我在这里没有看到的另一种技术是
SELECT * FROM table ORDER BY id DESC LIMIT 10000 OFFSET 1;
This will give you the records ordered by id descendant except first, that is except the last in the original order.
Note that with this method you will only take 10000 records, however this number can be as high as you want but cannot be omitted.
The advantage of this method is that you can order by whatever you want.
The disadvantage is that it gives you the records ordered from last to first.
Finally it worths noting that the other methods here works quite well
这将为您提供由id后代排序的记录，除了第一个，即原始顺序中的最后一个。请注意，使用此方法，您将只获取10000条记录，但是此数字可以根据需要增加，但不能省略。这种方法的优点是你可以按你想要的任何方式订购。缺点是它为您提供从上到下排序的记录。最后值得注意的是，这里的其他方法效果很好
#4
0
Another way to do this could be:
另一种方法是：
SELECT * FROM table WHERE ID <> LAST_INSERT_ID()
Reference: http://dev.mysql.com/doc/refman/5.7/en/getting-unique-id.html
参考：http：//dev.mysql.com/doc/refman/5.7/en/getting-unique-id.html
#1
15
Does the last row have the highest ID? If so, I think this would work:
最后一行的ID最高吗？如果是这样，我认为这会奏效：
SELECT * FROM TABLE WHERE ID != (SELECT MAX(ID) FROM TABLE)
MySQL does allow subselects in the current version, right?
MySQL确实允许当前版本中的子选择，对吗？
However, in most cases, it'd probably perform better if you selected all the rows and then filtered the unwanted data out in your application.
但是，在大多数情况下，如果您选择了所有行，然后在应用程序中过滤掉不需要的数据，它可能会表现得更好。
#2
2
SELECT DISTINCT t1.columns FROM table t1
INNER JOIN table t2 ON t1.id < t2.id
SELECT DISTINCT t1.columns FROM table t1 INNER JOIN table t2 ON t1.id
In my experience, MySQL loves this technique, going back several versions.
根据我的经验，MySQL喜欢这种技术，可以追溯到几个版本。
#3
1
Another technique I don't see listed here is
我在这里没有看到的另一种技术是
SELECT * FROM table ORDER BY id DESC LIMIT 10000 OFFSET 1;
This will give you the records ordered by id descendant except first, that is except the last in the original order.
Note that with this method you will only take 10000 records, however this number can be as high as you want but cannot be omitted.
The advantage of this method is that you can order by whatever you want.
The disadvantage is that it gives you the records ordered from last to first.
Finally it worths noting that the other methods here works quite well
这将为您提供由id后代排序的记录，除了第一个，即原始顺序中的最后一个。请注意，使用此方法，您将只获取10000条记录，但是此数字可以根据需要增加，但不能省略。这种方法的优点是你可以按你想要的任何方式订购。缺点是它为您提供从上到下排序的记录。最后值得注意的是，这里的其他方法效果很好
#4
0
Another way to do this could be:
另一种方法是：
SELECT * FROM table WHERE ID <> LAST_INSERT_ID()
Reference: http://dev.mysql.com/doc/refman/5.7/en/getting-unique-id.html
参考：http：//dev.mysql.com/doc/refman/5.7/en/getting-unique-id.html