在Mysql中添加“职位”号

Question

目前，我得到了以下查询和结果：

mysql> SELECT songname,COUNT(*) as count FROM etrstats WHERE songname != '' AND `when` >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)  GROUP BY songname ORDER BY count DESC LIMIT 5;
+--------------------------------------+-------+
| songname                             | count |
+--------------------------------------+-------+
| Meghan Trainor - All About That Bass |    67 |
| The Cars - Drive                     |    55 |
| Train - Drops Of Jupiter             |    54 |
| C.W. McCall - Convoy                 |    52 |
| The Script - Superheroes             |    48 |
+--------------------------------------+-------+

我想要做的是在查询中添加第二个临时字段，显示它在排序之后的位置。结果会是这样的：

+-----+--------------------------------------+-------+
| Pos | songname                             | count |
+-----+--------------------------------------+-------+
| 1   | Meghan Trainor - All About That Bass |    67 |
| 2   | The Cars - Drive                     |    55 |
| 3   | Train - Drops Of Jupiter             |    54 |
| 4   | C.W. McCall - Convoy                 |    52 |
| 5   | The Script - Superheroes             |    48 |
+-----+--------------------------------------+-------+

Answer 1

SET @x = 0;
SELECT (@x:=@x+1) Pos,* FROM
(
    SELECT songname,COUNT(*) as count FROM etrstats
    WHERE songname != '' AND `when` >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)
    GROUP BY songname ORDER BY count DESC LIMIT 5
) A;

试试看!！

我在其他帖子中增加临时变量

• Jun 08, 2012：更新大表上的排名
• Jun 03, 2013：嵌入秩选择