如何通过php从mysql获取row/array/assoc结果中的特定值中检索行号 - How to retrieve row number from specific value in a mysql fetch row/array/assoc result via php?

How to retrieve row number from specific value in a mysql fetch row/array/assoc result via php?

假设我在PHP中有这个sql结果（$myresults=mysql_fetch…assoc/row/array）：

SELECT table1.name as name, sum(table2.numbers) as numbers FROM table2 INNER JOIN
 table1 ON table2.fk_id = table1.id GROUP BY name ORDER BY numbers DESC
---------------
| John | 800  |
---------------
| Mark | 500  |
---------------
| Bill | 300  |
---------------

因此，我以Mark（$_SESSION['name']=="Mark"）的身份登录，我想知道值"Mark"位于第#行（在这种情况下，第1行，考虑到第一行是0）。

如何通过PHP获取？

谢谢…

编辑：把它想象成一个高分或排行榜表，我不需要用户id，但需要用户现在所在的行…

您应该这样使用用户定义的变量：

SELECT table1.name as name, sum(table2.numbers) as numbers,
  @rank := @rank + 1 rank
FROM table2
CROSS JOIN (SELECT @rank := 0) init
JOIN table1 ON table2.fk_id = table1.id
GROUP BY name
ORDER BY numbers DESC

仔细想想，分组可能会在UDV的计数方面给你带来一些麻烦。这是另一种选择，但性能不如以前的方法。

SELECT *, @rank := @rank + 1 rank FROM (
    SELECT table1.name as name, sum(table2.numbers) as numbers
    FROM table2
    JOIN table1 ON table2.fk_id = table1.id
    GROUP BY name
) s
CROSS JOIN (SELECT @rank := 0) init
ORDER BY numbers DESC

无论如何，我建议直接用PHP计数。这将更加灵活和高效。

修改SQL以选择主ID和其他数据：

SELECT 
    table1.id as id,
    table1.name as name, 
    sum(table2.numbers) as numbers 
FROM 
    table2 
INNER JOIN
    table1 ON table2.fk_id = table1.id 
GROUP BY 
    name 
ORDER BY 
    numbers DESC