MySQL变量没有正确递增

Question

运行以下查询

SET @rownum := 0;    
SELECT customer_id, @rownum := @rownum +1 AS rank, score
FROM game_table_customers gtc
INNER JOIN customers c ON c.customers_id = gtc.customer_id
INNER JOIN game_table gt ON gtc.table_id = gt.table_id
WHERE c.my_team =11095
AND gt.event_id =21110
ORDER BY score DESC
LIMIT 0 , 30

正确的结果应该是这样的

CustID  Rank    Score
2        1     130000
39       2     99426
84       3     99178
259      4     98963
339      5     97796

但是，我得到的是以下内容

CustID  Rank    Score
2        2      130000
39      11      99426
84      20      99178
259     54      98963
339     69  97796

当我排除 event_id 子句时，我得到了正确的结果。但是，当包含 event_id 时，它会扭曲它。我只尝试了 event_id(删除 my_team 子句)和相同的错误结果。

任何关于为什么结果可能会关闭的想法/建议将不胜感激

Answer 1

我想这与在每种情况下选择的执行计划以及与应用行号的混合有关 - 在其中一些计划中。计算行号，然后评估 WHERE 条件，因此您会看到不连续的行号。

试试这个(首先获得您需要的 30 个结果，然后应用行号):

SET @rownum := 0; 
SELECT customer_id, @rownum := @rownum +1 AS rank, score
FROM
  ( SELECT customer_id, score
    FROM game_table_customers gtc
      INNER JOIN customers c ON c.customers_id = gtc.customer_id
      INNER JOIN game_table gt ON gtc.table_id = gt.table_id
    WHERE c.my_team =11095
      AND gt.event_id =21110
    ORDER BY score DESC
    LIMIT 0 , 30
  ) tmp
ORDER BY score DESC

---

我现在无法测试，但你也可以试试这个:

SELECT customer_id, @rownum := @rownum +1 AS rank, score
FROM game_table_customers gtc
  INNER JOIN customers c ON c.customers_id = gtc.customer_id
  INNER JOIN game_table gt ON gtc.table_id = gt.table_id
  CROSS JOIN (SELECT @rownum := 0 AS rn) dummy  
WHERE c.my_team =11095
  AND gt.event_id =21110
ORDER BY score DESC
LIMIT 0 , 30