时间戳

首页 > 解决方案 > MYSQL - 使用 GROUP BY 和 INNER JOIN 从一个表中获取两个日期之间的记录

mysql - MYSQL - 使用 GROUP BY 和 INNER JOIN 从一个表中获取两个日期之间的记录

问题描述

我有一个下表：

SELECT * FROM dashboard.reports WHERE date='2020-04-06';

+------+--------------+----------+----------+----------+
| # id |     date     | type     | quantity |  vendor  |
+------+--------------+----------+----------+----------+
| '11' | '2020-04-06' | '2520'   | '150'    | 'vendor1'  |
| '12' | '2020-04-06' | 'HG851'  | '200'    | 'vendor2' |
| '13' | '2020-04-06' | 'HG851'  | '200'    | 'vendor2' |
| '14' | '2020-04-06' | 'HG851A' | '400'    | 'vendor2' |
+------+--------------+----------+----------+----------+

我必须按特定日期的 GROUP BY 供应商计算数量列的总数。所以下面是我的查询：

SELECT a.date, a.vendor, a.type, a.quantity, b.total FROM dashboard.reports a 
INNER JOIN 
(
    SELECT vendor, SUM(quantity) as total FROM dashboard.reports WHERE date = '2020-04-06' GROUP BY vendor
) b 
ON b.vendor = a.vendor WHERE date = '2020-04-06'

结果：（这里的总数是日期'2020-04-06'）

+--------------+----------+----------+----------+-------+
|    # date    |  vendor  | type | quantity | total |
+--------------+----------+----------+----------+-------+
| '2020-04-0'  | 'vendor1'  | '2520'   | '150'    | '150' |
| '2020-04-06' | 'vendor2' | 'HG851'  | '200'    | '800' |
| '2020-04-06' | 'vendor2' | 'HG851'  | '200'    | '800' |
| '2020-04-06' | 'vendor2' | 'HG851A' | '400'    | '800' |
+--------------+----------+----------+----------+-------+

vendor1 总计 ---> 150 vendor2 总计 (200 + 200 + 400) ----> 800

上述结果在某一天按预期工作。但是，如果我想获取两个日期之间的记录……我不确定如何编写查询。我试过低于一个，但我一整天都得到了总数。我想要每天的数量列的总数。

SELECT a.date, a.vendor, a.type, a.quantity, b.total FROM dashboard.reports a 
INNER JOIN 
(
    SELECT vendor, SUM(quantity) as total FROM dashboard.reports WHERE  date >= '2020-04-06' AND date <= '2020-04-08' GROUP BY vendor
) b 
ON b.vendor = a.vendor WHERE date >= '2020-04-06' AND date <= '2020-04-08'

+--------------+----------+-------------+----------+---------+
|    # date    |  vendor  |  type   | quantity |  total  |
+--------------+----------+-------------+----------+---------+
| '2020-04-06' | 'vendor1'  | '2520'      | '150'    | '1250'  |
| '2020-04-06' | 'vendor2' | 'HG851'     | '200'    | '1400'  |
| '2020-04-06' | 'vendor2' | 'HG851'     | '200'    | '1400'  |
| '2020-04-06' | 'vendor2' | 'HG851A'    | '400'    | '1400'  |
| '2020-04-07' | 'vendor1'  | '2511'      | '200'    | '1250'  |
| '2020-04-07' | 'vendor1'  | '5120'      | '350'    | '1250'  |
| '2020-04-07' | 'vendor1'  | '2520'      | '150'    | '1250'  |
| '2020-04-07' | 'vendor1'  | '5114'      | '400'    | '1250'  |
| '2020-04-07' | 'vendor3'  | 'G-440G-A'  | '200'    | '20750' |
| '2020-04-07' | 'vendor3'  | '1240GA'    | '400'    | '20750' |
| '2020-04-07' | 'vendor3'  | '1440GP'    | '9000'   | '20750' |
| '2020-04-07' | 'vendor3'  | 'B-0404G-B' | '7000'   | '20750' |
| '2020-04-07' | 'vendor3'  | 'B2404GP'   | '2000'   | '20750' |
| '2020-04-07' | 'vendor3'  | 'G-881G-A'  | '2000'   | '20750' |
| '2020-04-08' | 'vendor3'  | 'G-881G-B'  | '150'    | '20750' |
| '2020-04-08' | 'vendor2' | 'HG851'     | '200'    | '1400'  |
| '2020-04-08' | 'vendor2' | 'HG851A'    | '400'    | '1400'  |
+--------------+----------+-------------+----------+---------+

有人可以帮我弄这个吗？如果我上面写的查询完全错误，请指导我如何进一步进行。

标签： mysqlsqldatabasegroup-byinner-join

解决方案

也按日期分组。即：“按供应商、日期分组”

推荐阅读