none
求助(SQL相关) RRS feed

  • 问题

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

    如何能够展示为

    2011-01-31 星期一  10000

    2011-02-01 星期二 10000

    。。。。。

    第一周                   sum(sales)

    2011-02-07 星期一 2000

    。。。。

     

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

    烦请协助。。。。。

    2011年2月10日 10:34

答案

  • 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
    • 已标记为答案 allen00111 2011年2月12日 2:28
    2011年2月10日 11:20

全部回复

  • 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
    • 已标记为答案 allen00111 2011年2月12日 2:28
    2011年2月10日 11:20
  • 十分感谢

    2011年2月12日 2:28