选择按周分组的 SQL 结果
问题描述：
我想按周从下表中选择数据
I want to select data from following table group by weeks
Date Product Name Sale
+----------+--------------+-----+
14-05-11 a 2
14-05-11 b 4
17-05-11 c 3
19-05-11 a 6
24-05-11 a 6
29-05-11 a 6
假设今天是 30-05-11
所以我的结果应该是这样的.
So my result should look like this.
Product Name First Week Second Week Third Week
+--------------------+------------+------------+-------------+
a 12 6 2
b 0 0 4
c 0 3 0
有人会指导我如何编写 SQL 查询来实现这种行为！
Will some body guide me to how to write SQL query to achieve this behavior!
答
我认为应该这样做..
Select
ProductName,
WeekNumber,
sum(sale)
from
(
SELECT
ProductName,
DATEDIFF(week, '2011-05-30', date) AS WeekNumber,
sale
FROM table
)
GROUP BY
ProductName,
WeekNumber