--> 测试数据:[test] if object_id('[test]') is not null drop table [test] create table [test]( [courseID] int, [studentID] int, [startTime] datetime ) go insert [test] select 1,1,'2012-07-16' union all select 2,1,'2012-07-16' union all select 3,1,'2012-07-17' union all select 4,1,'2012-07-22' godeclare @StartDate datetime declare @EndDate datetime set @StartDate='2012-07-16' set @EndDate='2012-07-22';with t as( select DATEADD(DD,number,@StartDate) as Dtes from master..spt_values where 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 t left join test on t.Dtes=test.startTime group by Dtes order by 2 desc /* week count ------------------------------ 星期2 2 星期1 1 星期3 1 星期6 0 星期4 0 星期7 0 星期5 0 */
--> 测试数据:[test] if object_id('[test]') is not null drop table [test] create table [test]( [courseID] int, [studentID] int, [startTime] datetime ) go insert [test] select 1,1,'2012-07-16' union all select 2,1,'2012-07-16' union all select 3,1,'2012-07-17' union all select 4,1,'2012-07-22' godeclare @StartDate datetime declare @EndDate datetime set @StartDate='2012-07-16' set @EndDate='2012-07-22';with t as( select DATEADD(DD,number,@StartDate) as Dtes from master..spt_values where type='p' and number between 0 and DATEDIFF(DD,@StartDate,@EndDate) )select '星期'+ case when DATEPART(W,Dtes)=2 then '一' when DATEPART(W,Dtes)=3 then '二' when DATEPART(W,Dtes)=4 then '三' when DATEPART(W,Dtes)=5 then '四' when DATEPART(W,Dtes)=6 then '五' when DATEPART(W,Dtes)=7 then '六' else '日' end as [week], SUM(case when [startTime] is null then 0 else 1 end) as [count] from t left join test on t.Dtes=test.startTime group by Dtes order by 2 desc /* week count ---------------------------- 星期一 2 星期日 1 星期二 1 星期五 0 星期三 0 星期六 0 星期四 0 */
select a.wd,count(courseID) from ( select '星期一' as wd union all select '星期二' union all select '星期三' union all select '星期四' union all select '星期五' union all select '星期六' union all select '星期日' ) a left join courseInfo c on a.wd=datename(weekday ,c.startTime) group by a.wd
--> 测试数据:[courseInfo] IF OBJECT_ID('[courseInfo]') IS NOT NULL DROP TABLE [courseInfo] GO CREATE TABLE [courseInfo]([courseID] INT,[studentID] INT,[startTime] DATETIME) INSERT [courseInfo] SELECT 1,1,'2012-07-16' UNION ALL SELECT 2,1,'2012-07-16' UNION ALL SELECT 3,1,'2012-07-17' UNION ALL SELECT 4,1,'2012-07-22' GO--> 测试语句: select a.wd,count(courseID) as cnt from ( select '星期一' as wd union all select '星期二' union all select '星期三' union all select '星期四' union all select '星期五' union all select '星期六' union all select '星期日' ) a left join courseInfo c on a.wd=datename(weekday ,c.startTime) group by a.wd /*wd cnt ------ ----------- 星期一 2 星期二 1 星期三 0 星期四 0 星期五 0 星期六 0 星期日 1 警告: 聚合或其他 SET 操作消除了 Null 值。(7 行受影响)*/
才能帮你写SQL喔.
courseID studentID startTime
例:
courseID studentID startTime
1 1 2012-07-16
2 1 2012-07-16
3 1 2012-07-17
4 1 2012-07-22得出结果:
week count(courseID)
星期一 2
星期二 1
星期三 0
星期六 0
星期天 1
--> 测试数据:[test]
if object_id('[test]') is not null
drop table [test]
create table [test](
[courseID] int,
[studentID] int,
[startTime] datetime
)
go
insert [test]
select 1,1,'2012-07-16' union all
select 2,1,'2012-07-16' union all
select 3,1,'2012-07-17' union all
select 4,1,'2012-07-22'
godeclare @StartDate datetime
declare @EndDate datetime
set @StartDate='2012-07-16'
set @EndDate='2012-07-22';with t
as(
select
DATEADD(DD,number,@StartDate) as Dtes
from
master..spt_values
where
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
t
left join
test
on
t.Dtes=test.startTime
group by
Dtes
order by
2 desc
/*
week count
------------------------------
星期2 2
星期1 1
星期3 1
星期6 0
星期4 0
星期7 0
星期5 0
*/
--> 测试数据:[test]
if object_id('[test]') is not null
drop table [test]
create table [test](
[courseID] int,
[studentID] int,
[startTime] datetime
)
go
insert [test]
select 1,1,'2012-07-16' union all
select 2,1,'2012-07-16' union all
select 3,1,'2012-07-17' union all
select 4,1,'2012-07-22'
godeclare @StartDate datetime
declare @EndDate datetime
set @StartDate='2012-07-16'
set @EndDate='2012-07-22';with t
as(
select
DATEADD(DD,number,@StartDate) as Dtes
from
master..spt_values
where
type='p'
and number between 0 and DATEDIFF(DD,@StartDate,@EndDate)
)select
'星期'+
case when DATEPART(W,Dtes)=2 then '一'
when DATEPART(W,Dtes)=3 then '二'
when DATEPART(W,Dtes)=4 then '三'
when DATEPART(W,Dtes)=5 then '四'
when DATEPART(W,Dtes)=6 then '五'
when DATEPART(W,Dtes)=7 then '六'
else '日' end as [week],
SUM(case when [startTime] is null then 0 else 1 end) as [count]
from
t
left join
test
on
t.Dtes=test.startTime
group by
Dtes
order by
2 desc
/*
week count
----------------------------
星期一 2
星期日 1
星期二 1
星期五 0
星期三 0
星期六 0
星期四 0
*/
(
select '星期一' as wd union all
select '星期二' union all
select '星期三' union all
select '星期四' union all
select '星期五' union all
select '星期六' union all
select '星期日'
) a
left join courseInfo c on a.wd=datename(weekday ,c.startTime)
group by a.wd
IF OBJECT_ID('[courseInfo]') IS NOT NULL DROP TABLE [courseInfo]
GO
CREATE TABLE [courseInfo]([courseID] INT,[studentID] INT,[startTime] DATETIME)
INSERT [courseInfo]
SELECT 1,1,'2012-07-16' UNION ALL
SELECT 2,1,'2012-07-16' UNION ALL
SELECT 3,1,'2012-07-17' UNION ALL
SELECT 4,1,'2012-07-22'
GO--> 测试语句:
select a.wd,count(courseID) as cnt from
(
select '星期一' as wd union all
select '星期二' union all
select '星期三' union all
select '星期四' union all
select '星期五' union all
select '星期六' union all
select '星期日'
) a
left join courseInfo c on a.wd=datename(weekday ,c.startTime)
group by a.wd
/*wd cnt
------ -----------
星期一 2
星期二 1
星期三 0
星期四 0
星期五 0
星期六 0
星期日 1
警告: 聚合或其他 SET 操作消除了 Null 值。(7 行受影响)*/