如何用SQL排值日表，高手请进

Question

有一张学生表。
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 '龙九' 
要进行排值日表：如从2012-06-01 到2012-06-30进行排表：
得到如下结果：
日期          星期      name
2012-06-01   星期五    张三
-------------------------------------------星期六，星期天没有值班--------------
2012-06-04   星期一    李四
2012-06-05   星期二    王五
2012-06-06   星期三    赵六
2012-06-07   星期四    肖七
2012-06-08   星期五    谢八
2012-06-11   星期一    龙九
2012-06-12   星期二    张三
2012-06-12   星期三    李四
.依此类推
.
.
.
.说明：
1、要按student的name的id顺序去按日期轮流值班。
2、遇到星期六，星期天或者法定节日能跳过。

Answer 1

先建立一个工作日表tbday (fdate,fwork)再
select fdate,row_number() fno ... from tbday a
left join tbstudent b on (a.fno % 9)=b.fid

Answer 2

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 @tselect 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

Answer 3

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 studentselect 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 星期四 李四
*/
---节假日 你自己建表 然后 过滤即可。这里只过滤了 周六日

Answer 4

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 '龙九'  
declare @startDate date,@endDate dateselect @startDate='2012-06-01',@endDate='2012-06-30'declare @stu table(rn int identity(1,1), id int, name varchar(10))while((select count(1) from @stu)<=datediff(d,@startDate,@endDate))
begin
  insert into @stu(id,name) select id,name from student
end;with t as
(select cast(dateadd(d,number,@startDate) as date) dd,
        case datepart(dw,dateadd(d,number,@startDate)) 
        when 2 then '星期一' when 3 then '星期二'
        when 4 then '星期三' when 5 then '星期四'
        when 6 then '星期五' end 'dw' 
 from master.dbo.spt_values 
 where type='P' and 
 number between 0 and datediff(d,@startDate,@endDate)
 and datepart(dw,dateadd(d,number,@startDate)) not in(1,7)
)
select a.dd '日期',a.dw '星期',b.name
from (select row_number() over(order by dd) rn,dd,dw
from t) a 
inner join @stu b on a.rn=b.rn
/*
日期         星期     name
---------- ------ ----------
2012-06-01 星期五    张三
2012-06-04 星期一    李四
2012-06-05 星期二    王五
2012-06-06 星期三    赵六
2012-06-07 星期四    肖七
2012-06-08 星期五    谢八
2012-06-11 星期一    龙九
2012-06-12 星期二    张三
2012-06-13 星期三    李四
2012-06-14 星期四    王五
2012-06-15 星期五    赵六
2012-06-18 星期一    肖七
2012-06-19 星期二    谢八
2012-06-20 星期三    龙九
2012-06-21 星期四    张三
2012-06-22 星期五    李四
2012-06-25 星期一    王五
2012-06-26 星期二    赵六
2012-06-27 星期三    肖七
2012-06-28 星期四    谢八
2012-06-29 星期五    龙九(21 row(s) affected)
*/

Answer 5

USE Test
GOif 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 '龙九'  
DECLARE @StartDate DATETIME,@EndDate DATETIME,@Count INTSELECT @StartDate='2012-06-01'
,@EndDate='2012-06-30'
,@Count=COUNT(1)
FROM student;WITH c AS (
SELECT @StartDate AS Date
UNION ALL
SELECT DATEADD(dd,1,Date) FROM c
WHERE Date<@EndDate
),d AS (
SELECT Date,CASE WHEN ROW_NUMBER()OVER(ORDER BY Date)%@Count=0 THEN @Count ELSE ROW_NUMBER()OVER(ORDER BY Date)%@Count END AS Row FROM c 
WHERE DATENAME(weekday,Date) NOT IN('星期六','星期日')
)
SELECT 
d.Date AS 日期
,DATENAME(weekday,d.Date) AS 星期
,a.name 
FROM student AS a
INNER JOIN d ON a.id=d.Row 
ORDER BY Date ASC

Answer 6

-->>TravyLee生成测试数据
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 '龙九' 
go
if OBJECT_ID('pro_test')is not null
drop proc pro_test
go
create proc pro_test
(@startdate date,--开始日期
@enddate date)--结束日期
as
--创建表存放两个日期
CREATE TABLE TBL(
日期 DATE
)
INSERT TBL
SELECT @startdate UNION ALL
SELECT @enddate
declare @date date
select @date=MAX(日期) from tbl
--递归实现补全当中的日期
;with t
as(
select * from tbl
union all
select dateadd(dd,1,a.日期) from t a
where not exists(select * from tbl b
where b.日期=DATEADD(DD,1,a.日期)
)
and a.日期<@date
)
select 编号=ROW_NUMBER()over(order by 日期 asc),
'星期'+ltrim(DATEPART(W,日期)-1) as 星期,
日期 into #test from t 
where DATEPART(w,日期) not in(1,7)
order by 日期
declare @num int
set @num=(select COUNT(1) from student)
;with t
as(
select case when 编号%@num=0 
     then @num else 编号%7 end as px,
* from #test
)
select a.编号,a.日期,a.星期,b.name from t a
inner join student b
on a.px=b.id
order by 日期
--测试
exec pro_test '2012-06-01','2012-08-01'
/*
编号 日期 星期 name
--------------------------------------
1 2012-06-01 星期5 张三
2 2012-06-04 星期1 李四
3 2012-06-05 星期2 王五
4 2012-06-06 星期3 赵六
5 2012-06-07 星期4 肖七
6 2012-06-08 星期5 谢八
7 2012-06-11 星期1 龙九
8 2012-06-12 星期2 张三
9 2012-06-13 星期3 李四
10 2012-06-14 星期4 王五
11 2012-06-15 星期5 赵六
12 2012-06-18 星期1 肖七
13 2012-06-19 星期2 谢八
14 2012-06-20 星期3 龙九
15 2012-06-21 星期4 张三
16 2012-06-22 星期5 李四
17 2012-06-25 星期1 王五
18 2012-06-26 星期2 赵六
19 2012-06-27 星期3 肖七
20 2012-06-28 星期4 谢八
21 2012-06-29 星期5 龙九
22 2012-07-02 星期1 张三
23 2012-07-03 星期2 李四
24 2012-07-04 星期3 王五
25 2012-07-05 星期4 赵六
26 2012-07-06 星期5 肖七
27 2012-07-09 星期1 谢八
28 2012-07-10 星期2 龙九
29 2012-07-11 星期3 张三
30 2012-07-12 星期4 李四
31 2012-07-13 星期5 王五
32 2012-07-16 星期1 赵六
33 2012-07-17 星期2 肖七
34 2012-07-18 星期3 谢八
35 2012-07-19 星期4 龙九
36 2012-07-20 星期5 张三
37 2012-07-23 星期1 李四
38 2012-07-24 星期2 王五
39 2012-07-25 星期3 赵六
40 2012-07-26 星期4 肖七
41 2012-07-27 星期5 谢八
42 2012-07-30 星期1 龙九
43 2012-07-31 星期2 张三
44 2012-08-01 星期3 李四
*/