如何在mysql中获取行id

I want to get the row id of different idfeedback

我想获得不同idfeedback的行ID

SELECT  l.idfeedback_store,
    @curRow := @curRow + 1 AS row_number
FROM    feedback_store l
JOIN    (SELECT @curRow := 0) r;

Result

idfeedback_store        row_number
1                           1
1                           2
1                           3
2                           4
2                           5
2                           6
2                           7
3                           8
3                           9
3                          10
4                          11

Required Result

idfeedback_store        row_number
1                           1
1                           2
1                           3
2                           1
2                           2
2                           3
2                           4
3                           1
3                           2
3                           3
4                           1

3 个解决方案

#1

Try this:

SELECT  l.idfeedback_store, IF(@last=(@last:=idfeedback_store), @curRow := @curRow + 1, @curRow:=1) AS row_number
FROM feedback_store l, (SELECT @curRow := 0, @last:=0) r;

#2

You can use something similar to this:

你可以使用类似的东西:

select idfeedback_store, group_row_number
from 
(
  select idfeedback_store,
    @num := if(@idfeedback_store = `idfeedback_store`, @num + 1, 1) as group_row_number,
    @idfeedback_store := `idfeedback_store` as dummy
  from feedback_store
) src

See SQL Fiddle with Demo

请参阅SQL Fiddle with Demo

Result:

| IDFEEDBACK_STORE | GROUP_ROW_NUMBER |
---------------------------------------
|                1 |                1 |
|                1 |                2 |
|                1 |                3 |
|                2 |                1 |
|                2 |                2 |
|                2 |                3 |
|                2 |                4 |
|                3 |                1 |
|                3 |                2 |
|                3 |                3 |
|                4 |                1 |

#3

SELECT  
  idfeedback_store,
  @curRow := CASE WHEN @curStore = idfeedback_store THEN @curRow + 1 ELSE 1 END AS row_number,
  @curStore := idfeedback_store
FROM
  feedback_store;