数据库统计一周的数据，如果数据库中不存在这一周某天的数据，如何将这一天的值显示为0，并将一周的统计结果按星期排序
------解决方案--------------------
先产生每一周每一天的记录到一个临时表,再用这个表关联数据表的日期
------解决方案--------------------
首先把这一周的日期全部制定好格式表,再把这一周的汇总成你需要的信息,然后用日期关联显示就行了.
------解决方案--------------------
- SQL code
--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([courseID] int,[studentID] int,[startTime] datetime)goinsert [test]select 1,1,'2012-07-16' union allselect 2,1,'2012-07-16' union allselect 3,1,'2012-07-17' union allselect 4,1,'2012-07-22'godeclare @StartDate datetimedeclare @EndDate datetimeset @StartDate='2012-07-16'set @EndDate='2012-07-22';with tas(select DATEADD(DD,number,@StartDate) as Dtesfrom master..spt_valueswhere type='p' and number between 0 and DATEDIFF(DD,@StartDate,@EndDate))select '星期'+ltrim(DATEPART(W,Dtes)) as [week], SUM(case when [startTime] is null then 0 else 1 end) as [count]from tleft join test on t.Dtes=test.startTimegroup by Dtesorder by 2 desc/*week count------------------------------星期2 2星期1 1星期3 1星期6 0星期4 0星期7 0星期5 0*/
------解决方案--------------------
- SQL code
select a.wd,count(courseID) from(select '星期一' as wd union allselect '星期二' union allselect '星期三' union allselect '星期四' union allselect '星期五' union allselect '星期六' union allselect '星期日' ) aleft join courseInfo c on a.wd=datename(weekday ,c.startTime)group by a.wd