原文:
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(wk, datediff(wk,0,getdate()), 0),120) union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 1),120) union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 2),120) union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 3),120) union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 4),120) union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 5),120) union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 6),120)
示例：
如下图所示，有表MO_Orders，字段：BookTime,Number,Count，有如下数据。
查询出本周的每天的数据总和，语句如下：
with t as ( select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 0),120) union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 1),120) union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 2),120) union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 3),120) union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 4),120) union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 5),120) union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 6),120) ) select id=ROW_NUMBER()OVER(ORDER BY t1.date), DATENAME(weekday,t1.date) as weekday, t1.date, Numbers=sum(isnull(t2.Number,0)), Count=sum(isnull(t2.Count,0)) from t t1 left join ( select substring(convert(varchar,BookTime,120),1,11) as BookTime, Number,Count from MO_Orders where datediff(wk,BookTime-1,getdate()) = 0 ) t2 on t1.date= substring(convert(varchar,t2.BookTime,120),1,11) group by t1.date
查询效果如下图，其中 weekday为星期，此图还需替换，稍后补上：
示例：如下图所示，有表： 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
查询结果如下图：
