如何用SQL排值日表，高手请进
有一张学生表。
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、遇到星期六，星期天或者法定节日能跳过。
[解决办法]
- SQL code
declare @d table (fdate smalldatetime,fwork tinyint)declare @dt smalldatetimeset @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=0where DATEPART(dw,fdate) in (1,7)declare @t table (fid int IDENTITY(1,1),name varchar(10))insert into @t (name) select '张三' union allselect '李四' union allselect '王五' union allselect '赵六' union allselect '肖七' union allselect '谢八' union allselect '龙九' declare @n intselect @n=count(*) from @tselect a.fdate,b.name from(select fdate,row_number() over (order by fdate) as fn from @dwhere fwork=1) aleft join @t b on a.fn%@n+1=b.fidorder by fdate
[解决办法]
- SQL code
if OBJECT_ID('student') is not nulldrop table studentcreate table student (id int IDENTITY(1,1),name varchar(10))insert into student (name) select '张三' union allselect '李四' union allselect '王五' union allselect '赵六' union allselect '肖七' union allselect '谢八' union allselect '龙九' set datefirst 1declare @month_day intset @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 datetimeset @month_first_day= convert(varchar(10),dateadd(dd,-DatePart(day,dateadd(dd,-1,getdate())),getdate()),120)declare @count intselect @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 ) bbleft join student b on bb.RN=b.idorder by month_day/*month_day week_name name2012-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 星期四 李四*/---节假日 你自己建表 然后 过滤即可。这里只过滤了 周六日
------解决方案--------------------
- SQL code
create table student(id int IDENTITY(1,1),name varchar(10))insert into student (name) select '张三' union allselect '李四' union allselect '王五' union allselect '赵六' union allselect '肖七' union allselect '谢八' union allselect '龙九' 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 studentend;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.namefrom (select row_number() over(order by dd) rn,dd,dwfrom 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)*/
[解决办法]
- SQL code
USE TestGOif OBJECT_ID('student') is not nulldrop table studentcreate table student (id int IDENTITY(1,1),name varchar(10))insert into student (name) select '张三' union allselect '李四' union allselect '王五' union allselect '赵六' union allselect '肖七' union allselect '谢八' union allselect '龙九' 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
[解决办法]
- SQL code
-->>TravyLee生成测试数据if OBJECT_ID('student') is not nulldrop table studentcreate table student (id int IDENTITY(1,1),name varchar(10))insert into student (name) select '张三' union allselect '李四' union allselect '王五' union allselect '赵六' union allselect '肖七' union allselect '谢八' union allselect '龙九' goif OBJECT_ID('pro_test')is not nulldrop proc pro_testgocreate proc pro_test(@startdate date,--开始日期@enddate date)--结束日期as--创建表存放两个日期CREATE TABLE TBL(日期 DATE)INSERT TBLSELECT @startdate UNION ALLSELECT @enddatedeclare @date dateselect @date=MAX(日期) from tbl--递归实现补全当中的日期;with tas(select * from tblunion allselect dateadd(dd,1,a.日期) from t awhere not exists(select * from tbl bwhere 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 intset @num=(select COUNT(1) from student);with tas(select case when 编号%@num=0 then @num else 编号%7 end as px,* from #test)select a.编号,a.日期,a.星期,b.name from t ainner join student bon a.px=b.idorder 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 李四*/