mysql - 日期列表和另一个表的交叉连接

Question

我有一个查询，要求按日期范围从某个表中获取数据并按周分组。我的 CROSS JOIN 打算在日期范围内没有结果的情况下为每周填写一个默认值。

然后我可以执行这个查询。

    SELECT 
        SUM(invoice.amount) AS "invoice.amount", 
        CONCAT(DATE_FORMAT(invoice.updated_at, '%b %d'), ' - ', DATE_FORMAT(DATE_ADD(invoice.updated_at, INTERVAL 7 DAY), '%b %d')) AS "invoice.updated_at"
    FROM invoice
    CROSS JOIN (
        SELECT selected_date 
        FROM (
            SELECT ADDDATE('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date 
            FROM
                (SELECT 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
                (SELECT 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
                (SELECT 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
                (SELECT 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
                (SELECT 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
        ) v
        WHERE selected_date BETWEEN '2018-01-01' AND '2018-01-31'
        GROUP BY selected_date, YEAR(selected_date), WEEK(selected_date)
    ) calendar
    WHERE invoice.updated_at >= '2018-01-01' 
        AND invoice.updated_at <= '2018-01-31'
        AND invoice.status = "PAID"
    GROUP BY calendar.selected_date, invoice.id, invoice.amount, YEAR(invoice.updated_at), WEEK(invoice.updated_at)

假设我在数据库中有这些记录:

+----+------------+------------+------------+
| id |   amount   |   status   | updated_at | 
+----+------------+------------+------------+
|  1 |    1000    |    PAID    | 2018-01-01 |
|  2 |    2000    |    PAID    | 2018-01-01 |
|  3 |    100     |    PAID    | 2018-01-07 |
|  4 |    50      |    PAID    | 2018-01-11 |
+----+------------+------------+------------+

我希望看到这些结果，一月份的每周都有一个记录:

+--------+-------------------+
| amount |     updated_at    | 
+--------+-------------------+
|  3100  |  Jan 1  - Jan 7   |
|  50    |  Jan 8  - Jan 15  |
|  0     |  Jan 16 - Jan 22  |
|  0     |  Jan 23 - Jan 30  |
|  0     |  Jan 31 - Jan 31  |
+--------+-------------------+

但是，我得到了大约 50 个随机重复结果，其中最少包含连接的填充周，因为没有 0 数量:

+--------+----------------+
| amount |   updated_at   | 
+--------+----------------+
|  1000  | Jan 1 - Jan 7  |
|  2000  | Jan 1 - Jan 7  |
|  100   | Jan 1 - Jan 7  |
|  50    | Jan 8 - Jan 15 |
|  1000  | Jan 1 - Jan 7  |
|  1000  | Jan 1 - Jan 7  |
|  2000  | Jan 1 - Jan 7  |
|  2000  | Jan 1 - Jan 7  |
|  100   | Jan 1 - Jan 7  |
|  50    | Jan 8 - Jan 15 |
|  100   | Jan 1 - Jan 7  |
|  50    | Jan 8 - Jan 15 |
|  ...   | ...            |
|  ...   | ...            |
|  ...   | ...            |
+--------+----------------+

什么给出了？

Answer 1

GROUP BY calendar.selected_date, invoice.id, invoice.amount,

您在分组依据中指定了太多列，特别是 invoice.amount

相反，尝试使用:

GROUP BY
    CONCAT(DATE_FORMAT(invoice.updated_at, '%b %d'), ' - ', DATE_FORMAT(DATE_ADD(invoice.updated_at, INTERVAL 7 DAY), '%b %d')) 

<小时/>

我不能确定，但​​我认为您的日期范围也需要调整，以下内容将保证您获得 2018 年 1 月的所有内容:

WHERE invoice.updated_at >= '2018-01-01' 
    AND invoice.updated_at < '2018-02-01'