mysql - 按两列对记录进行分组

Question

我有名为“发票”的表。我想计算金额并按公司和日期进行分组

+------------+------------------+-------------+-----------+
| company_id | company          | date        | amount    |
+------------+--------------------------------+-----------+
|    1       | chevrolet        | 2017-11-18  |   100     |
|    1       | chevrolet        | 2017-11-18  |   -70     |
|    1       | chevrolet        | 2017-11-25  |   50      |
|    2       | mercedes         | 2017-04-01  |   30      |
|    2       | mercedes         | 2017-04-01  |   -30     |
|    2       | mercedes         | 2017-09-01  |   50      |
|    3       | toyota           | 2017-05-12  |   60      |
+------------+------------------+-------------+-----------+

期望的结果是:

+------------+------------------+-------------+-----------+
| company_id | model_name       | date        | amount    |
+------------+--------------------------------+-----------+
|    1       | chevrolet        | 2017-11-18  |   30      |
|    1       | chevrolet        | 2017-11-25  |   50      |
|    2       | mercedes         | 2017-04-01  |   0       |
|    2       | mercedes         | 2017-09-01  |   50      |
|    3       | toyota           | 2017-05-12  |   60      |
+------------+------------------+-------------+-----------+

我该怎么做？

Answer 1

您已经有英文规范，只需翻译为 SQL:

select company_id, model_name, date, sum(amount) as amount
from invoices
group by company_id, model_name, date

在 MySQL 中，您可以(取决于它的配置方式)无需执行 GROUP BY 行，并且您可能会在 MySQL 世界中看到这样的 SQL:

select company_id, model_name, date, sum(amount) as amount
from invoices

MySQL 会为您隐式插入分组依据。就我个人而言，我总是建议显式插入它，因为很少有其他数据库会执行“自动分组依据”，并且坚持使用标准 SQL 可以使您的 SQL 知识更便携。您可能还会发现“group by 应该始终是隐式的”论点的强烈支持者，我承认，它有其优点:)