时间戳

首页 > 解决方案 > 建立具有日期间隔的报告

mysql - 建立具有日期间隔的报告

问题描述

我需要根据卖家在特定日期范围内的销售情况进行报告。我遇到的问题是，报告还应该包含当天没有进行任何销售的卖家。

供应商（表）

id | name
----------
 1 | John
 2 | Tom

销售额（表）

id | vendor_id |    date        | status | price
------------------------------------------------
 1 |        1  |    2019-01-01  |    0   | 100
 2 |        1  |    2019-01-01  |    0   | 100 
 3 |        2  |    2019-01-01  |    0   | 100
 4 |        2  |    2019-01-02  |    0   | 100

所需报告：2019-01-01和2019-01-04之间的总销售额

date        |vendor| total
--------------------------
2019-01-01  | John | 200
2019-01-01  | Tom  | 100
2019-01-02  | John | 0
2019-01-02  | Tom  | 100
2019-01-03  | John | 0
2019-01-03  | Tom  | 0
2019-01-04  | John | 0
2019-01-04  | Tom  | 0

为了生成天数，我在这里使用日期生成器

当我交叉（左连接）生成的日期时，sales我得到以下信息：

SELECT date_generator.daily_date, vendors.name, sales.price
        FROM ( 
            SELECT DATE_ADD(@startDate, INTERVAL (@i:=@i + 1) - 1 DAY) AS daily_date   
            FROM information_schema.columns, (SELECT @i:=0) gen_sub   
            WHERE DATE_ADD(@startDate, INTERVAL @i DAY) BETWEEN @startDate AND @endDate 
        ) date_generator 
        LEFT JOIN sales ON DATE(sales.date) = DATE(date_generator.daily_date)
        LEFT JOIN vendors ON vendors.id = sales.vendor_id
ORDER BY date_generator.daily_date

date        |vendor| price
--------------------------
2019-01-01  | John | 100
2019-01-01  | John | 100
2019-01-01  | Tom  | 100
2019-01-02  | Tom  | 100
2019-01-03  | null | null
2019-01-04  | null | null

你能给我一些技巧来获得供应商和日期的分组吗？

标签： mysqlsql

解决方案

由于有些日子没有销售，您将有一个NULL sales.vendor.id价值，所以没什么可做JOIN的vendors。为确保您在所有日期获得所有供应商，您需要执行CROSS JOINfrom date_generatorto vendors，然后LEFT JOINto sales。然后，您需要获取SUM日期sales.price和GROUP BY供应商以获得所需的输出：

SELECT date_generator.daily_date, vendors.name, SUM(sales.price)
        FROM ( 
            SELECT DATE_ADD(@startDate, INTERVAL (@i:=@i + 1) - 1 DAY) AS daily_date   
            FROM information_schema.columns, (SELECT @i:=0) gen_sub   
            WHERE DATE_ADD(@startDate, INTERVAL @i DAY) BETWEEN @startDate AND @endDate 
        ) date_generator 
        CROSS JOIN vendors
        LEFT JOIN sales ON DATE(sales.date) = DATE(date_generator.daily_date) AND vendors.id = sales.vendor_id
GROUP BY date_generator.daily_date, vendors.name

推荐阅读