关于SQL group by week的一些问题

Question

我在按周编写 SQL 分组时遇到一些问题。

我有一个名为 order 的 MySQL 表。

在这个实体中，有几个属性，称为“order_id”，“order_date”，“amount”等。

我想制作一个表格来显示过去7天订单销售额的统计数据。

我想首先我应该得到今天的价值。

由于我使用Java Server Page，代码如下：

Calendar cal = Calendar.getInstance();
int day = cal.get(Calendar.DATE);
int Month = cal.get(Calendar.MONTH) + 1;
int year = cal.get(Calendar.YEAR);
String today = year + "-" + Month + "-" + day;

然后，我需要使用group by语句来计算过去7天的总销售额的SUM。

像这样：

    ResultSet rs=statement.executeQuery("select order_date, SUM(amount) " + 
    "from `testing`.`order` GROUP BY order_date");

我这里有问题。在我的SQL中，将显示所有order_date。

如何修改此 SQL 以便仅显示过去 7 天的订单销售额？

除此之外，我发现我原来的 SQL 存在问题。

也就是说，如果当天没有销售，则不会显示任何结果。

当然，我知道 ResultSet 不允许在我的 SQL 中返回空值。

我只是想知道我是否需要过去7笔订单的销售，即使金额是0美元，

我可以有其他方法来显示0吗？

如果您有想法，请给我建议。

谢谢。

Answer 1

通常会使用脚本或存储过程创建包含所有日期的日历表。

但是，如果您愿意，可以使用单个查询创建一个包含几个日期（在您的情况下是上周的日期）的表。

这是一个例子：

create table orders(
id int not null auto_increment primary key,
dorder date,
amount int
) engine = myisam;

insert into orders (dorder,amount)
values (curdate(),100),
       (curdate(),200),
       ('2011-02-24',50),
       ('2011-02-24',150),
       ('2011-02-22',10),
       ('2011-02-22',20),
       ('2011-02-22',30),
       ('2011-02-22',5),
       ('2011-02-19',10);

select t.cdate,sum(coalesce(o.amount,0)) as total 
from (
select curdate() - 
interval tmp.digit * 1 day as `cdate`
from (
select 0 as digit union all 
select 1 union all 
select 2 union all 
select 3 union all 
select 4 union all 
select 5 union all 
select 6 union all
select 7 ) as tmp) as t
left join orders as o
on t.cdate = o.dorder and o.dorder >= curdate() - interval 7 day
group by t.cdate
order by t.cdate desc

希望有帮助。问候。

Answer 2

回答您的问题“如何修改此 SQL，以便仅显示过去 7 天的订单销售额？”

通过添加 where 子句来修改 SQL 语句：

Where order_date >= @date_7days_ago

可以在语句之前设置此 @date_7days_ago 日期变量的值：

())

Select @date_7days_ago = dateadd(dd,-7, getdate 查询的 where 子句将仅返回订单日期在过去 7 天内的记录。

希望这有帮助。

Answer 3

您可以尝试使用这个：

ResultSet rs = statement.executeQuery(
  "SELECT IFNULL(SUM(amount),0) 
  FROM table `testing`.`order`
  WHERE order_date >= DATE_SUB('" + today + "', INTERVAL 7 DAY)"
);

这将为您提供过去 7 天内的订单数，如果没有则为 0。