declare @d table (fdate smalldatetime,fwork tinyint)
declare @dt smalldatetime
set @dt='2012-01-01'
insert @d (fdate,fwork) values (@dt,1)

while @dt<'2012-12-31'
begin
    set @dt=@dt+1
    insert @d (fdate,fwork) values (@dt,1)
end;

update @d set fwork=0
where DATEPART(dw,fdate) in (1,7)

declare @t table (fid int IDENTITY(1,1),name varchar(10))
insert into @t (name)  
select '张三' union all
select '李四' union all
select '王五' union all
select '赵六' union all
select '肖七' union all
select '谢八' union all
select '龙九'  

declare @n int
select @n=count(*) from @t

select a.fdate,b.name from
(
select fdate,row_number() over (order by fdate) as fn
 from @d
where fwork=1
) a
left join  @t b on a.fn%@n+1=b.fid
order by fdate

------解决方案--------------------

SQL code

if OBJECT_ID('student') is not null
drop table student
create table student (id int IDENTITY(1,1),name varchar(10))
insert into student (name)  
select '张三' union all
select '李四' union all
select '王五' union all
select '赵六' union all
select '肖七' union all
select '谢八' union all
select '龙九'  


set datefirst 1
declare @month_day int
set @month_day=datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-01' as datetime))))
declare @month_first_day datetime
set @month_first_day= convert(varchar(10),dateadd(dd,-DatePart(day,dateadd(dd,-1,getdate())),getdate()),120)


declare @count int
select @count=COUNT(1) from student

select month_day,week_name,name from
(
select month_day,week_name,
CASE WHEN row_number() over (order by getdate())%@count=0 THEN @count ELSE row_number() over (order by getdate())%@count END as RN
     from (select convert(varchar(10),dateadd(day,number,@month_first_day),120)as month_day,
                 DATENAME(weekday,convert(varchar(10),dateadd(day,number,@month_first_day),120)) as week_name,
                  datepart(weekday,convert(varchar(10),dateadd(day,number,@month_first_day),120)) as week_day
                 from master.dbo.spt_values 
                    where type='p' and number <@month_day 
                            and datepart(dw,dateadd(d,number,stuff(convert(varchar,getdate(),23),9,2,'01'))) not in(6,7))aa
    ) bb
left join  student b on bb.RN=b.id
order by month_day
/*
month_day    week_name    name
2012-05-01    星期二    张三
2012-05-02    星期三    李四
2012-05-03    星期四    王五
2012-05-04    星期五    赵六
2012-05-07    星期一    肖七
2012-05-08    星期二    谢八
2012-05-09    星期三    龙九
2012-05-10    星期四    张三
2012-05-11    星期五    李四
2012-05-14    星期一    王五
2012-05-15    星期二    赵六
2012-05-16    星期三    肖七
2012-05-17    星期四    谢八
2012-05-18    星期五    龙九
2012-05-21    星期一    张三
2012-05-22    星期二    李四
2012-05-23    星期三    王五
2012-05-24    星期四    赵六
2012-05-25    星期五    肖七
2012-05-28    星期一    谢八
2012-05-29    星期二    龙九
2012-05-30    星期三    张三
2012-05-31    星期四    李四
*/
---节假日 你自己建表 然后 过滤即可。这里只过滤了 周六日


                         怎么获得上年同期的日期？类似于2007-4-8是星期日，对应2006-4-9