我试图计算客户“分数”年份中每日数据的月总和（日期格式为％Y-％m-％d），但是每次我将查询运行到新表中时，都会得到一个结果总和不同，似乎跳过了随机日期。
看起来问题出在客户名称上。
我当前的查询：
INSERT INTO monthscore
(SELECT name, YEAR(date) as year, MONTH(date) as month, sum(score) as monthscore
FROM fastfoodSales
GROUP BY name, year, month)
因此，例如，某个客户在一个月内的实际得分为400,000，但是当我运行上述查询时，它输出380,000或399,000或390,000。自1960年以来，大约有100,000个客户使用数据。
fastfoodSales数据示例：
+----+---------+------------+--------+
| id | name | date | score |
+----+---------+------------+--------+
| 1 | Anthony | 2018-12-15 | 15.81 |
+----+---------+------------+--------+
| 2 | Jason | 2018-12-15 | 123.12 |
+----+---------+------------+--------+
| 3 | Chris | 2019-11-17 | 18.21 |
+----+---------+------------+--------+
| 4 | Chris | 2019-11-17 | 19.24 |
+----+---------+------------+--------+
| 5 | Chris | 2019-11-18 | 24.12 |
+----+---------+------------+--------+
| 6 | Jason | 2019-12-01 | 14.24 |
+----+---------+------------+--------+
| 7 | Jason | 2019-12-04 | 14.24 |
+----+---------+------------+--------+
| 8 | Anthony | 2019-12-14 | 81.14 |
+----+---------+------------+--------+
| 9 | Anthony | 2019-12-15 | 23.21 |
+----+---------+------------+--------+
CREATE TABLE `fastfoodSales` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(200) CHARACTER SET utf8 NOT NULL DEFAULT '',
`date` date DEFAULT NULL,
`score` double(18,2) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `indApril1` (`date`),
KEY `indnov29` (`name`),
KEY `indascore` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4152675 DEFAULT CHARSET=latin1
预期输出到monthscore表中：
+---------+------+-------+------------+
| name | year | month | monthscore |
+---------+------+-------+------------+
| Anthony | 2019 | 08 | 3213.23 |
| Anthony | 2019 | 09 | 12312.32 |
| Anthony | 2019 | 10 | 531.38 |
| Anthony | 2019 | 11 | 431.17 |
| Anthony | 2019 | 12 | 4314.34 |
| Jason | 2007 | 01 | 39.12 |
| Jason | 2007 | 02 | 2.1 |
| Jason | 2007 | 03 | 14.4 |
| Jason | 2007 | 04 | 23.20 |
+---------+------+-------+------------+
CREATE TABLE `monthscore` (
`name` varchar(200) CHARACTER SET utf8 NOT NULL DEFAULT '',
`year` int(4) DEFAULT NULL,
`month` int(2) DEFAULT NULL,
`score` double(19,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
但是我最终得到：
+---------+------+-------+------------+
| name | year | month | monthscore |
+---------+------+-------+------------+
| Anthony | 2019 | 08 | 2213.23 |
| Anthony | 2019 | 09 | 11312.32 |
| Anthony | 2019 | 10 | 531.38 |
| Anthony | 2019 | 11 | 431.17 |
| Anthony | 2019 | 12 | 4314.34 |
| Jason | 2007 | 01 | 1.12 |
| Jason | 2007 | 02 | 2.1 |
| Jason | 2007 | 03 | 14.4 |
| Jason | 2007 | 04 | 18.17 |
+---------+------+-------+------------+
最佳答案
您的查询似乎很好-但我不知道您为什么要存储派生数据...
DROP TABLE IF EXISTS fastfoodSales;
CREATE TABLE fastfoodsales
(id SERIAL PRIMARY KEY
,name VARCHAR(12) NOT NULL
,date DATE NOT NULL
,score DECIMAL(6,2) NOT NULL
);
INSERT INTO fastfoodSales (name,date,score) VALUES
('Anthony','2018-12-15',15.81),
('Jason','2018-12-15',123.12),
('Chris','2019-11-17',18.21),
('Chris','2019-11-17',19.24),
('Chris','2019-11-18',24.12),
('Jason','2019-12-01',14.24),
('Jason','2019-12-04',18.19),
('Anthony','2019-12-14',81.14),
('Anthony','2019-12-15',23.21);
SELECT name
, DATE_FORMAT(date,'%Y-%m') yearmonth
, SUM(score) monthscore
FROM fastfoodSales
GROUP
BY name
, yearmonth
+---------+-----------+------------+
| name | yearmonth | monthscore |
+---------+-----------+------------+
| Anthony | 2018-12 | 15.81 |
| Anthony | 2019-12 | 104.35 |
| Chris | 2019-11 | 61.57 |
| Jason | 2018-12 | 123.12 |
| Jason | 2019-12 | 32.43 |
+---------+-----------+------------+
关于mysql - 每月和每年的MySQL sum()值每次给我不同的结果，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/59438762/