sum()MySql的奇怪行为

Question

我试图求和，但结果很奇怪！

表结构：solde_user

id      int(10)
date    datetime
solde   varchar(50) latin1_swedish_ci
id_user int(10°

内部的数据

+--------+---------------------+----------+---------+
|   id   |        date         |  solde   | id_user |
+--------+---------------------+----------+---------+
| 127536 | 2020-12-15 03:26:02 | 465.0700 |   102   |
| 127311 | 2020-12-14 03:26:02 | 465.0700 |   102   |
+--------+---------------------+----------+---------+

我的请求

SELECT * FROM `solde_user` WHERE id_user = 102 ORDER BY date DESC LIMIT 1

返回

+--------+---------------------+----------+---------+
|   id   |        date         |  solde   | id_user |
+--------+---------------------+----------+---------+
| 127536 | 2020-12-15 03:26:02 | 465.0700 |   102   |
+--------+---------------------+----------+---------+

如果我写这个请求

SELECT id_user, SUM(solde) as sum FROM `solde_user` WHERE id_user = 102 ORDER BY date DESC LIMIT 1

回归太奇怪了-为什么

+---------+--------------------+
| id_user |        sum         |
+---------+--------------------+
|   102   | 405292.13999999996 | 
+---------+--------------------+

如果我把限制设置为2，回报是一样的我不明白为什么？？：(

Answer 1

首先，您需要一个group by子句来使代码成为有效的聚合查询。然后，您不应该将数字存储为字符串。最后，order by和limit在您的上下文中没有什么意义。它在聚合之后应用，而您的代码最多返回一行。

SELECT id_user, SUM(solde + 0) as sum_solde
FROM `solde_user` 
WHERE id_user = 102 
GROUP BY id_user

solde + 0强制转换为数字。