MySQL 你如何从mysql中选择每第n行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/858746/
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
How do you select every n-th row from mysql
提问by Corban Brook
I have a series of values in a database that I need to pull to create a line chart. Because i dont require high resolution I would like to resample the data by selecting every 5th row from the database.
我在数据库中有一系列值，我需要提取这些值来创建折线图。因为我不需要高分辨率，所以我想通过从数据库中选择每 5 行来重新采样数据。
回答by Taylor Leese
SELECT *
FROM (
SELECT
@row := @row +1 AS rownum, [column name]
FROM (
SELECT @row :=0) r, [table name]
) ranked
WHERE rownum % [n] = 1
回答by Owen
回答by Bill Karwin
Since you said you're using MySQL, you can use user variablesto create a continuous row numbering. You do have to put that in a derived table (subquery) though.
既然你说你使用的是 MySQL，你可以使用用户变量来创建连续的行编号。不过，您必须将其放在派生表（子查询）中。
SET @x := 0;
SELECT *
FROM (SELECT (@x:=@x+1) AS x, mt.* FROM mytable mt ORDER BY RAND()) t
WHERE x MOD 5 = 0;
I added ORDER BY RAND()to get a pseudorandom sampling, instead of allowing every fifth row of the unordered table to be in the sample every time.
我添加ORDER BY RAND()了一个伪随机抽样，而不是允许无序表的每第五行每次都在样本中。
An anonymous user tried to edit this to change x MOD 5 = 0to x MOD 5 = 1. I have changed it back to my original.
一位匿名用户试图将其编辑x MOD 5 = 0为x MOD 5 = 1。我已经把它改回我原来的了。
For the record, one can use any value between 0 and 4 in that condition, and there's no reason to prefer one value over another.
作为记录，在这种情况下可以使用 0 到 4 之间的任何值，并且没有理由偏爱一个值而不是另一个值。
回答by Andrey Kon
SET @a = 0;
SELECT * FROM t where (@a := @a + 1) % 2 = 0;
回答by Mark Richards
I had been looking for something like this. The answer of Taylor and Bill led me to improve upon their ideas.
我一直在寻找这样的东西。泰勒和比尔的回答使我改进了他们的想法。
table data1 has fields read_date, value we want to select every 2d record from a query limited by a read_date range the name of the derived table is arbitrary and here is called DT
表 data1 有字段 read_date, value 我们要从受 read_date 范围限制的查询中选择每条 2d 记录派生表的名称是任意的，这里称为 DT
query:
询问：
SET @row := 0;
SELECT * FROM ( SELECT @row := @row +1 AS rownum, read_date, value FROM data1
WHERE read_date>= 1279771200 AND read_date <= 1281844740 ) as DT WHERE MOD(rownum,2)=0
回答by Gor
SELECT *
FROM (
SELECT @row := @row +1 AS rownum, posts.*
FROM (
SELECT @row :=0) r, posts
) ranked
WHERE rownum %3 = 1
where posts is my table.
帖子是我的桌子。
回答by Mohideen bin Mohammed
You can use this query,
您可以使用此查询，
set @n=2; <!-- nth row -->
select * from (SELECT t.*,
@rowid := @rowid + 1 AS ID
FROM TABLE t,
(SELECT @rowid := 0) dummy) A where A.ID mod @n = 0;
or you can replace nwith your nth value
或者你可以用你的第 n 个值替换n
回答by Dharman
If you don't require the row number in the result set you can simplify the query.
如果您不需要结果集中的行号，您可以简化查询。
SELECT
[column name]
FROM
(SELECT @row:=0) temp,
[table name]
WHERE (@row:=@row + 1) % [n] = 1
Replace the following placeholders:
替换以下占位符：
- Replace
[column name]with a list of columns you need to fetch. - Replace
[table name]with the name of your table. - Replace
[n]with a number. e.g. if you need every 5th row, replace it with 5
- 替换
[column name]为您需要获取的列列表。 - 替换
[table name]为您的表的名称。 - 用
[n]数字代替。例如，如果您需要每 5 行，请将其替换为 5
回答by Richard Smith
If you're using MariaDB 10.2, MySQL 8 or later, you can do this more efficiency, and I think more clearly, using common table expressionsand window functions.
如果您使用的是 MariaDB 10.2、MySQL 8 或更高版本，您可以更高效地做到这一点，而且我认为更清晰，使用常见的表表达式和窗口函数。
WITH ordering AS (
SELECT ROW_NUMBER() OVER (ORDER BY name) AS n, example.*
FROM example ORDER BY name
)
SELECT * FROM ordering WHERE MOD(n, 5) = 0;
Conceptually, this creates a temporary table with the contents of the exampletable ordered by the namefield, adds an additional field called nwhich is the row number, and then fetches only those rows with numbers which are exactly divisible by 5, i.e. every 5th row. In practice, the database engine is often able to optimise this better than that. But even if it doesn't optimise it any further, I think it's clearer than using user variablesiteratively as you had to in earlier versions of MySQL.
从概念上讲，这将创建一个临时表，其中包含按example字段排序的表内容name，添加一个称为n行号的附加字段，然后仅获取那些数字可以被 5 整除的行，即每 5 行。在实践中，数据库引擎通常能够对此进行更好的优化。但即使它没有进一步优化它，我认为它比在早期版本的 MySQL 中迭代使用用户变量更清晰。
