选择月份以及该月所有天数的总和

Question

我的应用程序让用户将记录输入数据库。这些记录包含自动递增的 ID、日期（以 mm-dd-yyyy 格式的文本）、数量 (int) 和一些其他字段。

我正在尝试获取所有月份以及每个月的总数量。这样我就可以在我的应用程序中显示它们，例如：2010 年 12 月 - 90qty，2011 年 1 月 - 45qty...

我现在要做的是，按 id 选择日期，升序限制 1 和降序限制 1 来获取第一个和最后一个日期在数据库中。然后我分割月份和年份并计算其中有多少个月，然后我执行一个 for 循环并填充一个数组，这样我就有 12-2010、01-2011、02-2011...等，然后另一个 for 循环进行查询sum(quantity) 的数据库，其中日期在 12-01-2011 和 12-31-2011 之间，等等。

我想知道......有更好的方法吗？

Answer 1

CREATE TABLE test (
id integer primary key, date date, quantity integer);
INSERT INTO "test" VALUES(1,'2011-01-01',5);
INSERT INTO "test" VALUES(2,'2011-02-01',13);
INSERT INTO "test" VALUES(3,'2011-02-12',14);
INSERT INTO "test" VALUES(4,'2011-03-01',133);

您可以按月份分组并总结数量，如下所示。

select strftime('%Y-%m', date), sum(quantity) 
from test
group by strftime('%Y-%m', date);

2011-01    5
2011-02   27
2011-03  133

Answer 2

如果您可以将日期缩小到数据库中所需的粒度，那么您可以执行一个查询并迭代结果。这取决于您的 data 列的字段类型。例如，如果它是 VARCHAR，您可以使用 SUBSTRING 来挑选您想要的部分（年份和月份）。如果它是 DATETIME 你可以这样做：

SELECT strftime(date, '%m-%Y'), SUM(quantity)
FROM table GROUP BY strftime(date, '%m-%Y')