MySQL:如何获取行的序号? [英] MySQL: How to get a sequential number with rows?

问题描述

如何编号最低ID为#1而最高ID为#numberOfResults的结果

How can I number my results where the lowest ID is #1 and the highest ID is the #numberOfResults

示例:如果我有一个只有3行的表.其ID为24、87、112的ID会像这样拉:

Example: If I have a table with only 3 rows in it. whose ID's are 24, 87, 112 it would pull like this:

ID  24  87  112
Num 1   2   3

我想要这个的原因是我的经理想要对项目进行编号，例如item1，item2等.我最初是这样做的，所以它使用了ID，但他却看到它们像item24，item87，item112.他一点都不喜欢它，希望它们像item1，item2，item3.我个人认为这将导致问题，因为如果您要删除和添加项目，则item2不会总是引用相同的内容，并且可能会给用户造成混乱.因此，如果有人有更好的主意，我想听听.

The reason why I want this, is my manager wants items to be numbered like item1, item2, etc. I initially made it so it used the ID but he saw them like item24, item87, item112. He didn't like that at all and wants them to be like item1, item2, item3. I personally think this is going to lead to problems because if you are deleting and adding items, then item2 will not always refer to the same thing and may cause confusion for the users. So if anyone has a better idea I would like to hear it.

谢谢.

推荐答案

如果数字将用于除a以外的其他任何内容，我同意使用 not 这样的编号方案的注释带有数字的项目的简单有序显示.如果数字实际上要与某物联系在一起，那么这是一个非常糟糕的主意！

I agree with the comments about not using a numbering scheme like this if the numbers are going to be used for anything other than a simple ordered display of items with numbers. If the numbers are actually going to be tied to something, then this is a really bad idea!

使用变量，并在SELECT语句中将其递增:

Use a variable, and increment it in the SELECT statement:

SELECT
    id,
    (@row:=@row+1) AS row
FROM table,
(SELECT @row:=0) AS row_count;

示例:

CREATE TABLE `table1` (
    `id` int(11) NOT NULL auto_increment,
    PRIMARY KEY  (`id`)
) ENGINE=InnoDB

INSERT INTO table1 VALUES (24), (87), (112);

SELECT
    id,
    (@row:=@row+1) AS row
FROM table1,
(SELECT @row:=0) AS row_count;

+-----+------+
| id  | row  |
+-----+------+
|  24 |    1 |
|  87 |    2 |
| 112 |    3 |
+-----+------+

工作方式

@row是用户定义的变量.必须在主SELECT语句运行之前将其设置为零.可以这样完成:

@row is a user defined variable. It is necessary to set it to zero before the main SELECT statement runs. This can be done like this:

SELECT @row:=0;

或类似这样:

SET @row:=0

但是将两个语句结合在一起很方便.这可以通过创建派生表来完成，这是在这里发生的:

But it is handy to tie the two statements together. This can be done by creating a derived table, which is what happens here:

FROM table,
(SELECT @row:=0) AS row_count;

第二个SELECT实际上首先运行.完成此操作后，只需要为每个检索到的行增加@row的值即可:

The the second SELECT actually gets run first. Once that's done, it's just a case of incrementing the value of @row for every row retrieved:

@row:=@row+1

每次检索一行时，@row值都会增加.无论访问行的顺序如何，它始终会生成一个顺序的数字列表.因此，在某些情况下很方便，而在其他情况下很危险...

The @row value is incremented every time a row is retrieved. It will always generate a sequential list of numbers, no matter what order the rows are accessed. So it's handy for some things, and dangerous for other things...

这篇关于MySQL:如何获取行的序号?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！