选择在mysql中获取数据

Question

我的表结构

id     name 
1      bhagi
1      user
1      amit
1      sumit
2      bhagi
2      chinmay
2      sumit
3      rashmi
3      RBS
4      sumit
4      amit
4      bhagi
4      rashmi
4      RBS

I want result

id     name     result
1      bhagi    1/4(position of bhagi for id 1(1) / no of time that id is present(4))
1      user     2/4
1      amit     3/4
2      bhagi    1/3
2      chinmay  2/3
3      rashmi   1/2
4      sumit    1/5
4      amit     2/5
4      bhagi    3/5
4      rashmi   4/5

我的要求是得到结果列

示例:for Id 1

有4个id表示bhagi,user,amit和sumit

对于bhagi，

的结果将是1/4(因为bhagi在数据库中的位置是1,id 1的当前编号是4)。其他

也一样

，当结果为1时，我想忽略

我试过但不知道如何计算分母部分。表示该id出现的次数。

[编辑]

我试过了

 select id,
   name,
   IF(@LAST=(@LAST:=id), @CURROW := @CURROW + 1, @CURROW:=1) AS ROW_NUMBER
 FROM FEEDBACK_STORE 
 JOIN (SELECT @CURROW := 0, @LAST:=0) R

它给出的结果

id    name   row_number
1     bhagi    1
1     user     2
1     amit     3
1     sumit    4
2     bhagi    1
2     chinmay  2
2     sumit    3
3     rashmi   1
3     RBS      2
4     sumit    1
4     amit     2
4     bhagi    3
4     rashmi   4
4     RBS      5

我只需要id的分母部分的计数例如:for id 1-4,for id 2-3,for id 3-2,for id 4-5

(/编辑)

请帮

Thanks in advance

Answer 1

您可以使用子查询来计算具有相同id和较小或相等名称的行数:

select  t1.id
,       t1.name
,       concat(
        (
        select  count(*)
        from    Table1 t2
        where   t1.id = t2.id
                and t2.name <= t1.name
        ), '/', total.cnt)
from    Table1 t1
join    (
        select   id
        ,        count(*) as cnt
        from     Table1
        group by
                 id
        ) as total
on      t1.id = total.id
order by
        t1.id
,       t1.name

SQL Fiddle的实例

或者，使用更快但更脏的变量技巧:

select  t1.id
,       t1.name
,       concat(
            if(@last=(@last:=t1.id), @cur:=@cur+1, @cur:=1),
            '/',
            total.cnt)
from    Table1 t1
join    (
        select @cur := 0, @last:=0
        ) r
join    (
        select   id
        ,        count(*) as cnt
        from     Table1
        group by
                 id
        ) as total
on      t1.id = total.id