按天分组求和的MySQL Group By Sum

Question

我有一个表格：

id int pk auto_inc | created int(11) | amount int | user_id int

我希望创建一个按天分组的行列表，其中包括总金额字段。

我尝试过以下方法：

SELECT created, sum(amount) as amount, id FROM total_log WHERE user_id = $this->user_id GROUP BY DAY(created)

这个方法没有返回正确的结果。它们都被分组到同一行中。

日期以 dd/mm/yyyy 的格式保存，转换为 Unix 时间戳，例如 1349046000

- iamjonesy

---

应该有多行吗？您有 where user_id = $this->user_id。这让我想到，即使在结果集分组之前，也只有一行。 - Matt Fenwick

哎呀，我忘了提到 user_id。不行，同一个 user_id 有多条记录。 - iamjonesy

嗯...这个查询是否正确？在SQL Server中它甚至无法编译。SELECT子句引用了“created”和“id”列，而这些列在GROUP BY子句中被使用（day(created)在这些术语中与created是不同的）。 - pkmiec

@Dooh -- MySQL 允许这样做，不幸的是。 - Matt Fenwick

如果您将当前的 SELECT 更改为 SELECT DAY(created), SUM(amount)，会发生什么？ - Lamak

Answer 1

SELECT 
    DATE(FROM_UNIXTIME(created)) as d, 
    sum(amount) as amount
FROM total_log 
WHERE user_id = $this->user_id 
GROUP BY d

- frail

Answer 2

MySQL 不喜欢混合使用 day 和 int 列：

mysql> select day(1349046000);
+-----------------+
| day(1349046000) |
+-----------------+
|            NULL |
+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '1349046000' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

所以你所有的行都将具有NULL的day(some_int_value)，并且它们都在同一组中。

我建议使用date或datetime类型来代替该列。

此外，在group by子句中未列出的列不应在select语句中引用，除非对它们使用聚合函数。

- Matt Fenwick

Answer 3

尝试

SELECT 
DAY(DATE(FROM_UNIXTIME(created))), 
sum(amount) as amount 
FROM total_log 
WHERE user_id = $this->user_id 
GROUP BY DAY(DATE(FROM_UNIXTIME(created)))

- Yahia