MySQL tutorial: use variables to generate row numbers for each row

Usually when we use Mysql, we hope that the result set can also display the row number.

But there is no ROWNUM() function in Mysql, at this time we can implement it through variables.

For example, we have the following database tables:

+------+-------+
| name | score |
+------+-------+
| Jack | A     |
| Jack | A+    |
| Jack | C     |
| John | B     |
| John | A+    |
| John | C     |
| Mark | C     |
| Tom  | C     |
| Tom  | C     |
| Tom  | A     |
+------+-------+

Output result:

+------+------+--------+
| id   | name | score  |
+------+------+--------+
|    1 | Jack | A,A+,C |
|    2 | John | B,A+,C |
|    3 | Mark | C      |
|    4 | Tom  | C,C,A  |
+------+------+--------+

According to the field name, convert the field score column to row, and then output the row number of each row in the result set.

Method 1

Define a variable id variable implementation

• First, define a variable named @id and set its value to 0. The @id is a session variable indicated by the @ prefix.
• Then, group by the field name, and use the group_concat function to associate the field score.

mysql> set @id = 0;
mysql> select @id := @id + 1 as id, name, group_concat(score) as score from students group by name;

Method 2

mysql> select @id := @id + 1 as id, a.name, group_concat(a.score) as score from students as a, (select @id := 0) as b group by a.name;