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 fieldscore
.
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;