ff表
+-----------------------------+
+ value| name |Asort +
+-----------------------------+
+ 111 | Alpha |a +
+ 161 | Beta |b +
+ 092 | Delta |c +
+ 141 | Beta |a +
+ 113 | Beta |e +
+ 092 | Delta |f +
+ ... +
+ ... | more items +
+-----------------------------+
+-----------------------------+
+ value| name |Asort +
+-----------------------------+
+ 141 | Beta |a +
+ 161 | Beta |b +
+ 113 | Beta |c +
+ ... +
+ ... | more items(Beta) +
+-----------------------------+
我想返回"3"作为"113"行位置,其中按名称分组Beta和排序ASC按sort
你需要使用一个变量来计算排名(row-number-in-mysql),因为mySql没有ROW_NUMBER
。
SELECT ff.*,
@rownum := @rownum + 1 AS rank
FROM ff,
(SELECT @rownum := 0) r
WHERE name = 'Beta'
ORDER BY Asort