sql server 查詢本周、本月所有的天數

查詢本月所有的天數：

--本月所有的天數
select convert(varchar(10),dateadd(DAY,t2.number,t1.day),120) day from 
(select substring(convert(varchar,GETDATE(),120),1,7)+'-01' day) t1, 
(select number from MASTER..spt_values WHERE TYPE='P' AND number>=0 and number<=31) t2 
where convert(varchar(10),dateadd(DAY,t2.number,t1.day),120) like substring(convert(varchar,GETDATE(),120),1,7)+'%'

查詢本周所有的天數：

 select date=convert(varchar(10),dateadd(day,-4,getdate()),120)
 union all select date=convert(varchar(10),dateadd(day,-3,getdate()),120)
 union all select date=convert(varchar(10),dateadd(day,-2,getdate()),120)
 union all select date=convert(varchar(10),dateadd(day,-1,getdate()),120)
 union all select date=convert(varchar(10),getdate(),120)
 union all select date=convert(varchar(10),dateadd(day,+1,getdate()),120)
 union all select date=convert(varchar(10),dateadd(day,+2,getdate()),120)

示例：

如下圖所示，有表MO_Orders，欄位：BookTime,Number,Count。

查詢出本周的每天的數據總和。

查詢語句：

 with t as 
( 
 select date=convert(varchar(10),dateadd(day,-4,getdate()),120)
 union all select date=convert(varchar(10),dateadd(day,-3,getdate()),120)
 union all select date=convert(varchar(10),dateadd(day,-2,getdate()),120)
 union all select date=convert(varchar(10),dateadd(day,-1,getdate()),120)
 union all select date=convert(varchar(10),getdate(),120)
 union all select date=convert(varchar(10),dateadd(day,+1,getdate()),120)
 union all select date=convert(varchar(10),dateadd(day,+2,getdate()),120)
)
select id=ROW_NUMBER()OVER(ORDER BY t1.date),
        t1.date,
        Numbers=sum(isnull(t2.Number,0)),  
        Count=sum(isnull(t2.Count,0)) 
from t t1 
left join MO_Orders t2 
on t1.date= substring(convert(varchar,t2.BookTime,120),1,11)
group by t1.date

查詢效果如下圖，其中id為星期，從周一開始至周日：

示例：如下圖所示，有表： MO_Orders，欄位：BookTime，Cost，Count 。

查詢本月的所有數據總和(其中：total=Cost*Count)。

當把現有的數據總和之後如下圖：

查詢出每天的數據情況，查詢語句如下：

 with t as 
( 
  select convert(varchar(10),dateadd(DAY,t2.number,t1.day),120) date from 
    (select substring(convert(varchar,GETDATE(),120),1,7)+'-01' day) t1, 
    (select number from MASTER..spt_values WHERE TYPE='P' AND number>=0 and number<=31) t2 
    where convert(varchar(10),dateadd(DAY,t2.number,t1.day),120) like substring(convert(varchar,GETDATE(),120),1,7)+'%'
)
select id=ROW_NUMBER()OVER(ORDER BY t1.date),
        t1.date,
        CostTotal=sum(isnull(t2.CostTotal,0))
from t t1 
left join 
(    
    select BookTime,sum(CostTotal) as CostTotal from 
    (
        select substring(convert(varchar,BookTime,120),1,11) as BookTime,
                Cost*Count as CostTotal 
        from  MO_Orders 
        where datediff(month,BookTime,getdate()) = 0 
    ) o group by BookTime
) t2
on t1.date= t2.BookTime 
group by t1.date

查詢結果如下圖：