typescript - 选择每月分组日期范围内的销售额(SQLite)

Question

我需要查询获取没有销售的月份。 （SQLITE）

表

| id |    date    | total | id_user |
|  1 | 2019-01-01 | 400   |    1    |
|  2 | 2019-01-09 | 600   |    1    |
|  3 | 2019-08-01 | 100   |    1    |
|  4 | 2019-08-08 | 500   |    1    |
|  5 | 2019-08-15 | 1400  |    1    |
|  6 | 2019-09-01 | 7000  |    1    |

我有这个查询

SELECT 
    sum(venta.total) as Total,
    strftime('%m',venta.fecha_venta) as Month, 
    strftime('%Y',venta.fecha_venta) as Year
FROM venta 
LEFT JOIN VENTA_ESPECIE 
WHERE strftime('%Y', date('now')) == strftime('%Y',venta.fecha_venta) 
AND venta.fecha_venta 
BETWEEN '2019-01-30' AND '2019-10-30'
 GROUP by Month ;

结果

| Total | Month | Year |
| 1000  |  01   | 2019 |
| 2000  |  08   | 2019 |
| 7000  |  09   | 2019 |

我想得到这个结果

| Total | Month | Year |
| 1000  |  01   | 2019 |
|   0   |  02   | 2019 |
|   0   |  03   | 2019 |
|   0   |  04   | 2019 |
|   0   |  05   | 2019 |
|   0   |  06   | 2019 |
|   0   |  07   | 2019 |
| 2000  |  08   | 2019 |
| 7000  |  09   | 2019 |
|   0   |  10   | 2019 |

重要的是在两个日期之间可以更改

Answer 1

以下解决方案与@MikeT的解决方案类似，但是会计算最小和最大时间段。另外，输入表已被称为sales。

WITH monthly AS
       (SELECT strftime('%m', date) as Month, 
               strftime('%Y', date) as Year,
               total
        FROM sales),
     minmax AS
       (SELECT MIN(date) mn,
               MAX(date) mx
        FROM sales),
     cte_dates(dates) AS 
       (SELECT mn from minmax
        UNION ALL SELECT date(dates,'+1 Months')
                  FROM cte_dates WHERE dates <= (SELECT mx FROM minmax)),
     result AS
       (SELECT dates, total FROM cte_dates
        NATURAL LEFT JOIN
        (SELECT Year || '-' || Month || '-01' AS dates, SUM(total) total
         FROM monthly
         GROUP BY Year, Month))
SELECT COALESCE(total,0) as Total,
       strftime("%m", dates) as Month,
       strftime("%Y", dates) as Year
FROM result;