求助（SQL相关）

Question

我有表结构为 date name sales 的数据

如何能够展示为

2011-01-31 星期一  10000

2011-02-01 星期二 10000

。。。。。

第一周                   sum(sales)

2011-02-07 星期一 2000

。。。。

 

依次向下展示一个月的数据

烦请协助。。。。。

Answer 1

declare @table table
(	
	d date,
	sales int
)

insert into @table
select '2011-01-01',10000 union all
select '2011-01-02',10001 union all
select '2011-01-03',10002 union all
select '2011-01-04',10003 union all
select '2011-01-05',10044 union all
select '2011-01-11',10001 union all
select '2011-01-12',10001 union all
select '2011-01-13',10001 union all
select '2011-01-21',10002 union all
select '2011-01-23',10003 

select * from (

select '' as d,case when grouping(DATEPART(ww,d))=1 then 'all' else convert(varchar(20),DATEPART(ww,d)) end w,SUM(sales) sales from @table
group by DATEPART(ww,d) with cube
union all
select CONVERT(varchar(20),d,120) as d,convert(varchar(20),DATEPART(ww,d)) w, sales from @table

) t
order by w,d desc

/*
d          w          sales
-------------------- -------------------- -----------
2011-01-01      1          10000
           1          10000
2011-01-05      2          10044
2011-01-04      2          10003
2011-01-03      2          10002
2011-01-02      2          10001
           2          40050
2011-01-13      3          10001
2011-01-12      3          10001
2011-01-11      3          10001
           3          30003
2011-01-21      4          10002
           4          10002
2011-01-23      5          10003
           5          10003
           all         100058

(16 行受影响)


*/

ps.自己把数字周转换为汉字周吧

---

family as water

Answer 2

十分感谢