使用 MySQL，我如何在表中生成包含记录索引的列？

有没有什么方法可以从查询中得到实际的行号？

我希望能够通过一个名为 score 的字段订购一个名为 League _ girl 的表，并返回用户名和该用户名的实际行位置。

我想对用户进行排名，这样我就可以知道一个特定的用户在哪里，即。Joe 是200分中的100分。

User Score Row
Joe  100    1
Bob  50     2
Bill 10     3

我在这里看到了一些解决方案，但我已经尝试了其中的大部分，没有一个实际返回行号。

我试过这个:

SELECT position, username, score
FROM (SELECT @row := @row + 1 AS position, username, score
FROM league_girl GROUP BY username ORDER BY score DESC)

派生出来的

... 但它似乎没有返回行的位置。

有什么想法吗？

145342 次浏览
小开
最佳答案

You may want to try the following:

SELECT  l.position,
l.username,
l.score,
@curRow := @curRow + 1 AS row_number
FROM    league_girl l
JOIN    (SELECT @curRow := 0) r;

The JOIN (SELECT @curRow := 0) part allows the variable initialization without requiring a separate SET command.

Test case:

CREATE TABLE league_girl (position int, username varchar(10), score int);
INSERT INTO league_girl VALUES (1, 'a', 10);
INSERT INTO league_girl VALUES (2, 'b', 25);
INSERT INTO league_girl VALUES (3, 'c', 75);
INSERT INTO league_girl VALUES (4, 'd', 25);
INSERT INTO league_girl VALUES (5, 'e', 55);
INSERT INTO league_girl VALUES (6, 'f', 80);
INSERT INTO league_girl VALUES (7, 'g', 15);

Test query:

SELECT  l.position,
l.username,
l.score,
@curRow := @curRow + 1 AS row_number
FROM    league_girl l
JOIN    (SELECT @curRow := 0) r
WHERE   l.score > 50;

Result:

+----------+----------+-------+------------+
| position | username | score | row_number |
+----------+----------+-------+------------+
|        3 | c        |    75 |          1 |
|        5 | e        |    55 |          2 |
|        6 | f        |    80 |          3 |
+----------+----------+-------+------------+
3 rows in set (0.00 sec)
小开 
SELECT @i:=@i+1 AS iterator, t.*
FROM tablename t,(SELECT @i:=0) foo
小开

Assuming MySQL supports it, you can easily do this with a standard SQL subquery:

select
(count(*) from league_girl l1 where l2.score > l1.score and l1.id <> l2.id) as position,
username,
score
from league_girl l2
order by score;

For large amounts of displayed results, this will be a bit slow and you will want to switch to a self join instead.

小开

You can also use

SELECT @curRow := ifnull(@curRow,0) + 1 Row, ...

to initialise the counter variable.

小开

If you just want to know the position of one specific user after order by field score, you can simply select all row from your table where field score is higher than the current user score. And use row number returned + 1 to know which position of this current user.

Assuming that your table is league_girl and your primary field is id, you can use this:

SELECT count(id) + 1 as rank from league_girl where score > <your_user_score>
小开

Here comes the structure of template I used:

  select
/*this is a row number counter*/
( select @rownum := @rownum + 1 from ( select @rownum := 0 ) d2 )
as rownumber,
d3.*
from
( select d1.* from table_name d1 ) d3

And here is my working code:

select
( select @rownum := @rownum + 1 from ( select @rownum := 0 ) d2 )
as rownumber,
d3.*
from
(   select     year( d1.date ), month( d1.date ), count( d1.id )
from       maindatabase d1
where      ( ( d1.date >= '2013-01-01' ) and ( d1.date <= '2014-12-31' ) )
group by   YEAR( d1.date ), MONTH( d1.date ) ) d3
小开

I know the OP is asking for a mysql answer but since I found the other answers not working for me,

  • Most of them fail with order by
  • Or they are simply very inefficient and make your query very slow for a fat table

So to save time for others like me, just index the row after retrieving them from database

example in PHP:

$users = UserRepository::loadAllUsersAndSortByScore();


foreach($users as $index=>&$user){
$user['rank'] = $index+1;
}

example in PHP using offset and limit for paging:

$limit = 20; //page size
$offset = 3; //page number


$users = UserRepository::loadAllUsersAndSortByScore();


foreach($users as $index=>&$user){
$user['rank'] = $index+1+($limit*($offset-1));
}
小开

I found the original answer incredibly helpful but I also wanted to grab a certain set of rows based on the row numbers I was inserting. As such, I wrapped the entire original answer in a subquery so that I could reference the row number I was inserting.

SELECT * FROM
(
SELECT *, @curRow := @curRow + 1 AS "row_number"
FROM db.tableName, (SELECT @curRow := 0) r
) as temp
WHERE temp.row_number BETWEEN 1 and 10;

Having a subquery in a subquery is not very efficient, so it would be worth testing whether you get a better result by having your SQL server handle this query, or fetching the entire table and having the application/web server manipulate the rows after the fact.

Personally my SQL server isn't overly busy, so having it handle the nested subqueries was preferable.


提交答案