因此，我试图找出每个月(在本例中为 6 月至 7 月)销售额变化最大的客户。

这是我为这个练习创建的模型数据:

mysql> select * from Sales1;
+------------+------------+-----------------+
| CustomerID | mydate     | purchase_amount |
+------------+------------+-----------------+
|         10 | 1996-08-02 |         2540.78 |
|         20 | 1999-01-30 |         1800.54 |
|         30 | 1995-07-14 |          460.33 |
|         10 | 1998-06-29 |            2400 |
|         50 | 1998-02-03 |          600.28 |
|         60 | 1998-03-02 |             720 |
|         10 | 1998-07-06 |             150 |
+------------+------------+-----------------+
mysql> select * from Sales2;
+------------+------------+-----------------+
| CustomerID | mydate     | purchase_amount |
+------------+------------+-----------------+
|         10 | 1996-06-02 |          540.78 |
|         20 | 1999-09-30 |          800.54 |
|         30 | 1995-07-14 |           60.33 |
|         40 | 1998-01-29 |             400 |
|         10 | 1998-07-03 |         2600.28 |
|         60 | 1998-03-02 |            1720 |
|         70 | 1998-05-04 |            4150 |
+------------+------------+-----------------+

根据以上两张表，答案应该是1998年CustomerID为10且6月至7月销售额增加350.28的客户。

下面是我实现目标的代码；基本上我创建了两个 View ，一个包含每年每个客户的所有 JUNE 销售额总和，另一个包含每个客户的所有 JULY 销售额总和年，然后从 JULY 销售额中减去 JUNE 销售额:

CREATE VIEW sum6 AS
(
SELECT CustomerID, 
YEAR(mydate) AS year, 
MONTH(mydate) AS month,
SUM(purchase_amount) as amount
FROM Sales1
GROUP BY CustomerID, year, month
HAVING month = 6
) 
UNION ALL (
SELECT CustomerID,
YEAR(mydate) AS year, 
MONTH(mydate) AS month,
SUM(purchase_amount) as amount
FROM Sales2
GROUP BY CustomerID, year, month
HAVING month = 6) 
;

CREATE VIEW sum7 AS
(
SELECT CustomerID, 
YEAR(mydate) AS year, 
MONTH(mydate) AS month,
SUM(purchase_amount) as amount
FROM Sales1
GROUP BY CustomerID, year, month
HAVING month = 7
) 
UNION ALL (
SELECT CustomerID,
YEAR(mydate) AS year, 
MONTH(mydate) AS month,
SUM(purchase_amount) as amount
FROM Sales2
GROUP BY CustomerID, year, month
HAVING month = 7) 
;

SELECT CustomerID, year, (SUM(sum7.amount)-SUM(sum6.amount)) as diff
FROM sum6
JOIN sum7
USING(CustomerID, year)
GROUP BY CustomerID, year
;

但是，我的输出是:

+------------+------+--------------------+
| CustomerID | year | diff               |
+------------+------+--------------------+
|         10 | 1998 | -2049.719970703125 |
+------------+------+--------------------+

虽然是，但 CustomerID 和年份值是正确的，差异金额不正确。

我分别检查了 sum6 和 sum7 的总和是否按 CustomerID 和年份正确计算:

mysql> SELECT CustomerID, year, SUM(amount)
    -> FROM sum7
    -> GROUP BY CustomerID, year
    -> ;
+------------+------+-------------------+
| CustomerID | year | SUM(amount)       |
+------------+------+-------------------+
|         10 | 1998 | 2750.280029296875 |
|         30 | 1995 | 520.6599884033203 |
+------------+------+-------------------+
mysql> SELECT CustomerID, year, SUM(amount)
    -> FROM sum6
    -> GROUP BY CustomerID, year
    -> ;
+------------+------+------------------+
| CustomerID | year | SUM(amount)      |
+------------+------+------------------+
|         10 | 1996 | 540.780029296875 |
|         10 | 1998 |             2400 |
+------------+------+------------------+

是的，所以我知道 GROUP BY 是正确的。

然后我试着查看个人金额:

mysql> SELECT CustomerID, year, SUM(sum7.amount), SUM(sum6.amount)
    -> FROM sum6
    -> JOIN sum7
    -> USING(CustomerID, year)
    -> GROUP BY CustomerID, year
    -> ;
+------------+------+-------------------+------------------+
| CustomerID | year | SUM(sum7.amount)  | SUM(sum6.amount) |
+------------+------+-------------------+------------------+
|         10 | 1998 | 2750.280029296875 |             4800 |
+------------+------+-------------------+------------------+

所以 SUM(sum7.amount) 是正确的，但 SUM(sum6.amount) 是不正确的。但是为什么单独拉的时候加起来是对的，合起来只有一个加起来不对呢？这种不一致让我抓狂......

最佳答案

您的JOIN 语句不完整。

您将 sum6 连接到 sum7 过于松散。要使用您的最后一个案例，您的 JOIN 正在以某种方式复制记录。 (2400 * 2 = 4800)

当您合计它们时，由于连接的设置方式，您会以某种方式从其中一个 View 中获取重复记录。您需要检查您的 conditions

为了帮助缩小范围，包括所有行并且在您可以验证数据之前不进行数学运算。从以下内容开始:

SELECT *
FROM sum6
JOIN sum7
USING(CustomerID, year)

并验证只有您要配对的行正在配对，然后从那里开始。

关于MySQL:聚合结果不正确但不确定为什么？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/40664085/