MySQL $i++ 等价物 [英] MySQL $i++ equivalent

问题描述

我正在使用的查询:

SELECT COUNT(*),
    SUM(amount) AS amount,
    FROM_UNIXTIME(added, '%W (%e/%m)') AS daily
FROM affiliates_earnings
WHERE added >= '1319074836'
    AND added <= '1319679636'
GROUP BY daily

输出:

+----------+--------+-------------------+
| COUNT(*) | amount | daily             |
+----------+--------+-------------------+
|        1 |    195 | Tuesday (25/10)   |
|        4 |    470 | Wednesday (26/10) |
+----------+--------+-------------------+

我想显示的内容:

+-------+----------+--------+-------------------+
| i     |COUNT(*)  | amount | daily             |
+-------+----------+--------+-------------------+
| 1     |        1 |    195 | Tuesday (25/10)   |
| 2     |        4 |    470 | Wednesday (26/10) |
+-------+----------+--------+-------------------+

本质上 i 将是序列号(即 1,2,3,4,5,6...).

Essentially i would be the sequential number (i.e. 1,2,3,4,5,6...).

我将如何做到这一点?

推荐答案

没有 ROW_NUMBER()，你可以伪造它 根据这个答案 或 这个:

Without ROW_NUMBER(), you can fake it per this answer or this one:

  SELECT @i := @i + 1 AS i, COUNT(*), ...
    FROM affiliates_earnings
    JOIN (SELECT @i := 0) dummy
   WHERE ...
GROUP BY ...;

这篇关于MySQL $i++ 等价物的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！