疯狂值班表（人员跟日期生成的视图）

第一种人员表跟日期表（人员轮流值班）

上面是按照指定到了人员值班，自动查找的人员值班，以下是代码

--用户表中字段：id,login_name.数据如下：
select id,login_name from Au_user;

--假设指定用户从id=54开始轮流值班,并且按id升序轮流值班。则先对用户表进行序号排序
select row_number()over(order by seq asc, id asc )-1 as RowNum,* from 
(
	select id,login_name,0 as seq from au_user where id>=54 
	union ALL
	select id,login_name,1 as seq from au_user where id<54 
) t1 

-- 假设日期表为：temp_table_date
select * from temp_table_date;

-- 假设日期为2至10号,从小日期至大日期轮流值日:
--给日期表设置序号
select RowNum%(select count(1) from Au_user) as r_row_num,date_field from 
(
	select row_number()over(order by date_field asc )-1 as RowNum,date_field from temp_table_date
) t2
;

--组合起来，结果如下：
select * from 
(
		select row_number()over(order by seq asc, id asc )-1 as RowNum,id,login_name from 
		(
			select id,login_name,0 as seq from au_user where id>=54 
			union ALL
			select id,login_name,1 as seq from au_user where id<54 
		) t1 
) V1
LEFT JOIN 
(
		select RowNum%(select count(1) from Au_user) as r_row_num,date_field from 
		(
			select row_number()over(order by date_field asc )-1 as RowNum,date_field from temp_table_date
		) t2
) V2 ON V1.RowNum=V2.r_row_num 
ORDER BY V2.date_field ASC

　　第二种，有班级，人员表关联班级，所产生的值班数据

 

 mod(v1.RowNum,v2.user_count)  改为v1.RowNum%v2.user_count （具体看情况用）

 获取分组序号，这个知识点很关键row_number()over(PARTITION  by CLASS_ID order by ORDER_ desc ,id asc )  

以上数据代码如下

select r1.class_id,r1.class_name,r1.CURRENT_USER_ID,R2.ID AS Login_id,r2.login_name,r1.date_field from (
select v1.class_id,v1.class_name,V1.CURRENT_USER_ID,v1.date_field,v2.user_count,v1.RowNum,mod(v1.RowNum,v2.user_count) as class_mod from 
(select t1.ID as class_id,t1.CLASS_NAME,t1.CURRENT_USER_ID,t2.RowNum,t2.DATE_FIELD from  
    view_table_class t1 ,
    (select row_number()over(order by date_field asc )-1 as RowNum,date_field from temp_table_date) t2 
) v1
left join 
(select class_id,count(1) as user_count from AU_USER group by CLASS_ID) v2
on v1.class_id =v2.class_id
) r1
left join (
    select row_number()over(PARTITION  by CLASS_ID order by ORDER_ desc ,id asc )-1 as RowNum,v.* from 
    (
        select   t1.id,t1.LOGIN_NAME,t1.CLASS_ID,t2.CLASS_NAME,t2.CURRENT_USER_ID,
        case when t1.id>=t2.CURRENT_USER_ID then 1 else 0 end as ORDER_ from au_user t1 left join view_table_class t2 on t1.CLASS_ID=t2.ID 
    ) v
) r2 on R1.CLASS_ID=R2.CLASS_ID AND r1.class_mod=r2.RowNum
order by r1.class_id ASC,DATE_FIELD ASC

　　

 

 

 

 之前代码优化一下，换成以下代码： 

-- 查询结果
select r1.class_id,r1.class_name,r1.CURRENT_USER_ID,R2.ID AS Login_id,r2.login_name,r1.date_field from (
    select v1.id as class_id,v1.class_name,V1.CURRENT_USER_ID,v1.date_field,v2.user_count,v1.RowNum,mod(v1.RowNum,v2.user_count) as class_mod 
    from (
        select row_number()over(PARTITION  by id order by date_field asc )-1 as RowNum,t1.*,t2.*
        from view_table_class t1,temp_table_date  t2
    ) v1 left join 
    (select class_id,count(1) as user_count from AU_USER group by CLASS_ID) v2
    on v1.id =v2.class_id
) r1
left join (
    select row_number()over(PARTITION  by CLASS_ID order by ORDER_ desc ,id asc )-1 as RowNum,v.* from 
    (
        select   t1.id,t1.LOGIN_NAME,t1.CLASS_ID,t2.CLASS_NAME,t2.CURRENT_USER_ID,
        case when t1.id>=t2.CURRENT_USER_ID then 1 else 0 end as ORDER_ from au_user t1 left join view_table_class t2 on t1.CLASS_ID=t2.ID 
    ) v
) r2 on R1.CLASS_ID=R2.CLASS_ID AND r1.class_mod=r2.RowNum
order by r1.class_id ASC,DATE_FIELD ASC

　　原创作品，转载请在明显位置写明原创地址