• 教师值班表


    --教师值班
    alter proc teacher_OnDuty(@hid int)
    as
    set nocount on
    begin
    declare @bdate datetime,@edate datetime,@i int,@count_num int,@teacher_num varchar(50),@rand int,@j int,@cfmax int
    DECLARE @templist TABLE(id int IDENTITY(1,1),date DATETIME,teacher1 varchar(50),tid1 int,teacher2 varchar(50),tid2 int)
    declare @teacher table(id int IDENTITY(1,1),teacher varchar(50))
    create table #rand_teacher(id int IDENTITY(1,1),teacher varchar(50),oldid int)
    declare @cfid table(id int)

    --2个时间段取出时间序列表
    SET @i=1
    select @bdate=bdate,@edate=edate FROM dwd_185 WHERE hid=@hid
    WHILE(@bdate<=@edate)
    BEGIN
    INSERT INTO @templist(date)
    SELECT @bdate
    SET @bdate=DATEADD(dd,1,@bdate)
    SET @i=@i+1
    end
    --总数
    select @count_num=count(1) from @templist
    --所有老师一次
    insert into @teacher(teacher)
    select EnglishName from teacherinfo where state=0
    --辅课老师二次
    insert into @teacher(teacher)
    select EnglishName from teacherinfo where state=0 and CourseType=1
    --教师总数
    select @teacher_num=count(1) from @teacher
    --随机教师
    set @i=1

    insert into #rand_teacher(teacher,oldid)
    select teacher,id from @teacher order by newid()
    while(@i<=@teacher_num)
    begin
    if exists(select teacher from #rand_teacher where id=@i or id=@i+1 group by teacher having count(*)>1)
    begin
    truncate table #rand_teacher
    insert into #rand_teacher(teacher,oldid)
    select teacher,id from @teacher order by newid()
    set @i=1
    continue
    end
    set @i=@i+1
    end
    -- while(@i<=@teacher_num)
    -- begin
    -- select @rand=cast(ceiling(rand() * @teacher_num) as int)
    -- if not exists(select top 1 1 from #rand_teacher a inner join @teacher b on a.oldid=b.id where a.oldid=@rand)
    -- begin
    -- insert into #rand_teacher(teacher,oldid)
    -- select teacher,id from @teacher where id=@rand
    -- set @i=@i+1
    -- end
    -- end
    --填入时间
    set @j=1
    set @i=1
    -- while(@i<=@count_num+1)
    -- begin
    while(@j<=5)
    begin
    update @templist set teacher1=a.teacher,tid1=a.id from #rand_teacher a,@templist b where a.id=@i and b.id=@j
    update @templist set teacher2=a.teacher,tid2=a.id from #rand_teacher a,@templist b where a.id=@i+1 and b.id=@j
    set @i=@i+2
    set @j=@j+1
    end
    -- end
    -- if exists(select top 1 1 from @templist a where a.teacher1=a.teacher2)
    -- begin
    -- insert into @cfid(id)
    -- select id from @templist a where a.teacher1=a.teacher2
    -- set @i=@i+1
    -- select @cfmax=count(1) from @cfid
    -- set @j=1
    -- while(@j<=@cfmax)
    -- begin
    -- update @templist set teacher2=a.teacher,tid2=a.id from #rand_teacher a,@templist b,@cfid c where b.id=@j
    -- set @j=@j+1
    -- end
    -- end
    -- if exists(select top 1 1 from @templist a where a.teacher1=a.teacher2)
    -- begin
    -- select '1'
    -- end
    drop table #rand_teacher
    insert into tonduty(date,Teacher1,Teacher2)
    select date,teacher1,teacher2 from @templist
    end

    每天一进步、一积累,创造自我价值,体现人生逼格,你是自己的赢家!
  • 相关阅读:
    文本检测和识别 代码结构梳理
    UnicodeDecodeError: 'utf-8' codec can't decode byte
    GPU 显存释放
    DCM 图片查看
    hive SQL 字母大小写转换
    vim常用命令之多行注释和多行删除
    js 模拟call、apply、bind实现
    CommonJS、AMD、CMD和ES6模块化区别
    js setTimeout setInterval 第三个参数说明
    js instanceof 实现原理
  • 原文地址:https://www.cnblogs.com/chlf/p/4269595.html
Copyright © 2020-2023  润新知