TSQL:按周对客户订单进行分组 [英] TSQL: grouping customer orders by week

问题描述

我有一张包含订单集合的表格.这些字段是:

I have a table with a collection of orders. The fields are:

• customerName(文本)
• DateOfOrder(日期时间).

• customerName (text)
• DateOfOrder (datetime).

我想显示每位客户每周的订单总数.我想把它安排在每周的星期五，这样它看起来像这样:

I would like to show totals of orders per week per customer. I would like to have it arranged for the Friday of each week so that it looks like this:

all dates follow mm/dd/yyyy

"bobs pizza", 3/5/2010, 10
"the phone co",3/5/2010,5
"bobs pizza", 3/12/2010, 3
"the phone co",3/12/2010,11

有人可以告诉我怎么做吗?

Could somebody please show me how to do this?

谢谢

推荐答案

创建一个可以更轻松地分组的字段.

Make a field which you can group by more easily.

SELECT COUNT(OrderID), WeekStart
FROM 
(
 SELECT *, 
  dateadd(week, datediff(day,'20000107',yourDate) / 7, '20000107') AS WeekStart  
 FROM Orders
) o
GROUP BY WeekStart;

20000107 是已知的星期五.

20000107 is a known Friday.

这篇关于TSQL:按周对客户订单进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！