MySQL $i++等效项
提问于 2011-10-27 02:06:14
我正在使用的查询：
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……)。
我该如何做到这一点呢？
回答 2
Stack Overflow用户
回答已采纳
发布于 2011-10-27 02:23:21
如果没有ROW_NUMBER()，您可以用per this answer或this one来伪造它
SELECT @i := @i + 1 AS i, COUNT(*), ...
FROM affiliates_earnings
JOIN (SELECT @i := 0) dummy
WHERE ...
GROUP BY ...;Stack Overflow用户
发布于 2011-10-27 02:25:41
这样做几乎是胡说八道，但如果你真的想这样做，试试这样做：
SELECT
@i:=@i+1 as i,
COUNT(*),
SUM(a.amount) AS amount,
FROM_UNIXTIME(a.added, '%W (%e/%m)') AS daily
FROM affiliates_earnings a, (SELECT @i:=0) b
WHERE a.added >= '1319074836'
AND a.added <= '1319679636'
GROUP BY a.daily页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/7910989
复制相关文章
点击加载更多
腾讯云开发者
