如何选择除前3条记录外的所有记录? [英] How can select all records excepting first 3 records?
问题描述
我有一列
id
-----
1
32
3
6
5
22
54
21
问题1:如何从列中选择除前3条记录以外的所有记录?
Ques2:如何选择最后3条记录?
-谢谢.
您基本上需要将此类查询放入限制.您不能在纯SQL中使用子选择或变量.在存储过程中，可以使用变量.
这很有效，很遗憾，我无法在 sqlfiddle 中显示它，因为它们似乎对存储过程的支持有限. /p>
drop procedure if exists all_but_3;
delimiter //
create procedure all_but_3()
begin
declare v_max bigint unsigned default ~0;
select * from your_table limit 3, v_max;
end//
delimiter ;
drop procedure if exists last_3;
delimiter //
create procedure last_3()
begin
declare v_max bigint;
declare v_mid bigint;
select count(*) from your_table into v_max;
set v_mid := v_max - 3;
select * from your_table limit v_mid, v_max;
end//
delimiter ;
call all_but_3();
call last_3();
关于InnoDB聚集索引的详细说明
在与@fthiella的其他答案之一进行讨论之后，我决定详细说明其工作方式.
使用InnoDB作为引擎的表将始终具有聚集索引.总是.它们是将数据存储在InnoDB中的方式，并且无法以任何方式创建没有聚簇索引的表.
如果有一个或第一个唯一索引且所有列均设置为非null，则InnoDB将选择主键.如果不存在这样的索引，InnoDB将创建一个具有行ID的隐藏列.此行ID的工作方式与自动递增类似，并且如果有助于将其视为具有自动递增的不可见列，我认为还可以.
InnoDB还将根据所使用的索引返回行.它将始终使用某些索引(检索数据的唯一方法是使用辅助索引，聚集索引或组合索引)，因此在没有显式创建的索引的情况下，隐藏的聚集索引将返回行.
这意味着对没有主键，没有唯一索引且所有列都设置为非null且没有ORDER BY的表的查询将按插入顺序返回行.
这个问题就是这种情况，也是我的答案和许多其他答案的基础.
我并不是说这是处理数据的好方法.使用此解决方案之前，您应该考虑以下几点:
- 如果曾经创建过可以用作聚集索引的索引，则该表将被重写以使用该索引，并以此方式对磁盘上的数据进行排序.如果以后删除索引，则原始的插入顺序将丢失并且无法检索.
- 如果创建了索引，即使它不是唯一的，也可以由优化程序选择要使用的索引，而行将由该索引排序.
所有这些都已记录下来，对于5.5，这是 解决方案
You basically need to put such queries into stored procedures due to some limitations on LIMIT. You can't use sub selects or variables in plain sql. In stored procedures you can use variables.
This works, unfortunately I can't show it in sqlfiddle cause they seem to have limited support for stored procedures.
drop procedure if exists all_but_3;
delimiter //
create procedure all_but_3()
begin
declare v_max bigint unsigned default ~0;
select * from your_table limit 3, v_max;
end//
delimiter ;
drop procedure if exists last_3;
delimiter //
create procedure last_3()
begin
declare v_max bigint;
declare v_mid bigint;
select count(*) from your_table into v_max;
set v_mid := v_max - 3;
select * from your_table limit v_mid, v_max;
end//
delimiter ;
call all_but_3();
call last_3();
Elaboration on InnoDB Clustered Indexes
After discussions in one of the other answers with @fthiella I've decided to elaborate some on how this can work.
A table using InnoDB as engine will always have a clustered index. Always. It's they way data is stored in InnoDB and it's not in any way possible to create a table without a clustered index.
InnoDB will choose the primary key if there is one or the first unique index with all columns set to not null. If no such index exists InnoDB will create a hidden column with a row id. This row id works similar to auto increment and if it helps to think about it as a invisible column with auto increment I think that is fine.
Further will InnoDB return rows according to the index used. It will always use some index (only way to retrieve data is to use either a secondary index, the clustered index or a combination) so in the case where there are no explicitly created indexes rows are returned by the hidden clustered index.
This means that a query against a table with no primary key and no unique indexes with all columns set to not null and no ORDER BY will return rows in the order they where inserted.
This is the case for this question and the base for mine and many other answers.
I don't mean to say this is a good way to work with the data. Here are some things that you should think about before using this solution:
- If a index that can be used as a clustered index is ever created the table will be rewritten to use that index and by doing so order the data on disk. If the index is later dropped the original insert order is lost and cannot be retrieved.
- If a index is created, even if it's not unique, it could be chosen by the optimizer to be used and the rows will be ordered by that index instead.
All this is documented and for 5.5 it's the 3rd bullet points on this page
这篇关于如何选择除前3条记录外的所有记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
