网站建设猫腻,网站设计是做什么的,腾讯外贸电商平台,上海十大国企集团row_number 和 cte 使用实例#xff1a;考场监考安排 考场监考安排使用 cte 模拟两个表的原始数据使用 master..spt_values 进行数据填充优先安排时长较长的考试使用 cte 安排第一个需要安排的科目统计老师已有的监考时长尝试使用 cte 递归#xff0c;进行下一场考试安排考场监考安排 考场监考安排使用 cte 模拟两个表的原始数据使用 master..spt_values 进行数据填充优先安排时长较长的考试使用 cte 安排第一个需要安排的科目统计老师已有的监考时长尝试使用 cte 递归进行下一场考试安排尝试了三个方向才成功第一个尝试使用考试科目递归失败第二个尝试使用老师数据及考试批次递归失败第三次尝试用 for xml 继承数据 最后用字符串切割和列转行排出监考排班表 小结 考场监考安排
问题出自问答区 python 频道的一个问答原问答地址https://ask.csdn.net/questions/7901104。
题主对提问的方式不太熟悉他其实已经提了一系列关于监考安排这个问题的问答了每次都略有遗漏所以老顾把完整的需求从新描述一下。 有若干场不同科目的考试每个科目的考试时长不同有若干位不同科目的老师去监考每个考试需要1名监考老师监考老师不得监考同科目的考试。 请尽量安排老师所用的总监考时长为最平均的接近值。 题主这里给出了29位不同科目的老师还有77场不同科目的考试。老顾用 python 通过编程的方式已经实现了这个需求平均每位老师监考总时长在200至250之间。
但是这个题目老顾觉得用数据库查询的方式也可以做于是就拿来练练手。
使用 cte 模拟两个表的原始数据
with 考试 as (select 语文 科目,13 场次,120 时长union all select 英语,16,100union all select 数学,16,100union all select 物理,8,60union all select 道法,8,60union all select 历史,8,50union all select 化学,8,50
),老师 as (select 语文 科目,4 numunion all select 数学,5union all select 英语,6union all select 物理,3union all select 道法,3union all select 历史,3union all select 生物,3union all select 体育,2
)
select * from 考试使用 master…spt_values 进行数据填充
因为题主给的数据就是这样的所以咱也不知道具体每个老师叫啥每个科目啥时候举行有没有时间冲突咱就当他没有好了。但是这样的汇总数据是无法进行计算的我们需要把所有的汇总数据展开成单项。这里使用了mssql系统库中自带的一个数据表master…spt_values使用这个表可以很方便的扩展成单项进行数据补全。相信看过我以前 sql 文章的小伙伴对这个已经很熟悉了。
with ... -- 前边的 with 不再重复
,teacher as (select 科目,number from 老师cross apply (select number from master..spt_valueswhere typep and number between 1 and num) b
)
select * from teacher很容易就得到了单个老师的排列及科目了。
在这里不得不说cross/outer apply 很好用可以直接引用前边查询表中的列而 mysql 则未提供这种方式写个关联查询为了引用前边的列也很费劲。
优先安排时长较长的考试
这个是一个朴素的想法先把废时间的安排出去然后谁的总时长少谁就多安排一点这样的话平均时长就差不多了所以我们对考试场次按时长做个倒序分配序号来确定分配优先级并作为 cte 递归的依据。
with ...
,exam as (select *,row_number() over(order by 时长 desc) sn from 考试
)
select * from exam使用 cte 安排第一个需要安排的科目
第一个需要安排的科目自然是语文了根据 sn 得出的结论嘛。
我们需要安排13个非语文科目的老师进行语文考试的监考为了每次安排不那么一样还要用 newid() 函数协助一下随机排序那么大概得指令就是这样了。
with ...
,sc as (select b.*,a.科目 as 监考,时长,a.sn from exam across apply (select *,row_number() over(order by newid()) nidfrom teacherwhere 科目a.科目) bwhere sn 1 and nid场次
)
select * from sc第一个科目很容易就安排出去了现在准备攻坚了递归之后的科目。
统计老师已有的监考时长
在递归之前我们先考虑一下对之后的安排我们需要做两件事 1、对所有老师的已安排监考的时长进行统计选择时长最短的老师进行下一场监考安排 2、递归数据需要排重不能粗暴的直接用 sn a.sn 1 这样的方式了因为我们已经有了13场 sn 等于1的安排了 先按老师的时长统计进行一下计算我们修改下 select * from sc 的内容看看这29位老师每个人的第一场考试后的时长。
select a.*,isnull(总时长,0) 总时长
from teacher a
left join (select 科目,number,sum(时长) 总时长 from scgroup by 科目,number
) b on a.科目b.科目 and a.numberb.number尝试使用 cte 递归进行下一场考试安排尝试了三个方向才成功 由于在 cte 递归中无法使用 top 或 offset 之类的运算所以可以看到我们刚才实现第一场的内容时都是用的 row_number 做的变动这里我们同样使用 row_number 变通一下 变动过程中是各种报错啊。。。。嘿嘿
第一个尝试使用考试科目递归失败
,sc as (select convert(nvarchar(max),b.科目) 科目,number,时长,a.科目 as 监考,a.sn from exam across apply (select *,row_number() over(order by newid()) nidfrom teacherwhere 科目a.科目) bwhere sn 1 and nid场次union allselect convert(nvarchar(max),下一场),0,场次,e.科目,e.snfrom (select *,row_number() over(order by sn desc) rid from sc) ainner join exam e on a.sn1e.snwhere rid1)
select * from sc
order by sn不出预料的每个下一场都是13场和语文一样的场次老顾是没办法解决这个问题了只好换个思路。
第二个尝试使用老师数据及考试批次递归失败
with ...
,sc as (select 科目,number,(case when nid场次 then 用时时长 else 用时 end) 用时,(case when nid场次 then 监考 else null end) 监考,批次,场次,nidfrom (select t.*,时长,e.科目 监考,1 批次,场次,row_number() over(order by 场次 desc,newid()) nidfrom teacher tleft join exam e on t.科目e.科目 and e.sn1) aunion allselect 科目,number,(case when nid场次 then 用时时长 else 用时 end) 用时,(case when nid场次 then 监考 else null end) 监考,批次,场次,row_number() over(order by rowcount)from (select s.科目,number,用时,e.科目 监考,批次 1 批次,e.场次,时长,row_number() over(order by (case when s.科目e.科目 then 1 else 0 end),用时,newid()) nidfrom sc s,exam ewhere s.批次1 e.sn) a
)
select * from sc
order by 批次,nid结果发现。。。。cte 递归原来是一行数据一行数据的递归下来的不是整个表一起递归的难怪不让用 join 之类的指令。由于是一行一行递归的所以这里的 row_number 得不到1之外的序号了如果再次引入 teacher 表则无法继承考试用时了只好再换个思路。
第三次尝试用 for xml 继承数据
,sc as (select * from exam across apply (select convert(nvarchar(max),stuff((select ; 科目 : convert(varchar,number) : convert(varchar,(case when nid场次 then 时长 else 0 end))from (select t.*,时长,e.科目 监考,1 批次,场次,row_number() over(order by 场次 desc,newid()) nidfrom teacher tleft join exam e on t.科目e.科目 and e.sna.sn) a--where nid 场次for xml path()),1,1,)) z) bwhere a.sn1union allselect b.*,convert(nvarchar(max),)from sc a,exam bwhere a.sn 1 b.sn
)
select * from sc可以看到我们可以把所有老师的用时都放到组合数据了然后用切割字符串方式再回复成表数据进行计算这样就可以继续尝试了。
这里老顾就直接用正则了毕竟切割的话还是比较麻烦的需要好几次列转行我用正则可以省略一次。。。。如果有小伙伴想在 mssql 里使用正则可以看老顾以前的文章或者直接下载老顾准备好的 clr 指令《mssql正则clr及函数追加Group分组支持》然后直接运行里边的指令就可以使用老顾下边的正则指令了。 竟然还有这么多不让用的pivot 也不能用就有点麻烦了好在老顾的正则不错可以单独提取数据不用 pivot 也可以。
with ...
,sc as (select * from exam across apply (select convert(nvarchar(max),stuff((select ; 科目 : convert(varchar,number) : convert(varchar,时长)from (select t.*,时长,e.科目 监考,1 批次,场次,row_number() over(order by 场次 desc,newid()) nidfrom teacher tleft join exam e on t.科目e.科目 and e.sna.sn) awhere nid 场次for xml path()),1,1,)) curr) bcross apply (select convert(nvarchar(max),stuff((select ; 科目 : convert(varchar,number) : convert(varchar,(case when nid 场次 then 时长 else 0 end))from (select t.*,时长,e.科目 监考,1 批次,场次,row_number() over(order by 场次 desc,newid()) nidfrom teacher tleft join exam e on t.科目e.科目 and e.sna.sn) afor xml path()),1,1,)) sc) cwhere a.sn1union allselect 科目,场次,时长,sn,c,z from (select b.*,a.scfrom sc a,exam bwhere a.sn 1 b.sn) across apply (select stuff((select ; tn : tsn : convert(varchar,(case when nid场次 then 用时时长 else 用时 end))from (select *,row_number() over(order by 用时,newid()) nid from (select master.dbo.RegexMatch(match,[^:]) tn,master.dbo.RegexMatch(match,(?:)\d(?:)) tsn,convert(int,master.dbo.RegexMatch(match,(?:)\d(?$))) 用时from master.dbo.RegexMatches(sc,([^;:]):(\d):(\d))) a) afor xml path()),1,1,) z) bcross apply (select stuff((select ; tn : tsn : convert(varchar,用时)from (select *,row_number() over(order by 用时,newid()) nid from (select master.dbo.RegexMatch(match,[^:]) tn,master.dbo.RegexMatch(match,(?:)\d(?:)) tsn,convert(int,master.dbo.RegexMatch(match,(?:)\d(?$))) 用时from master.dbo.RegexMatches(sc,([^;:]):(\d):(\d))) a) awhere nid场次for xml path()),1,1,) c) c
)
select * from sc嗯最后一行 sc 是所有老师的用时情况每一行的 curr 是该科目考试所安排的老师我们先看看总用时情况 历史:1:230;生物:3:230;数学:4:250;道法:3:250;语文:3:250;数学:2:250;生物:1:250;数学:1:250;体育:1:200;道法:2:200;语文:1:200;物理:3:200;英语:2:200;历史:2:200;语文:4:200;语文:2:200;英语:3:200;物理:1:200;英语:4:230;英语:5:230;英语:1:230;体育:2:230;历史:3:230;数学:5:230;物理:2:230;数学:3:230;英语:6:240;道法:1:240;生物:2:240 一共 29 个老师每个老师200到250的用时结果验证正确真不容易为了绕过限制这次的指令写的很复杂如果有小伙伴想了解详细内容可以在评论区扣我。
最后用字符串切割和列转行排出监考排班表
with 考试 as (select 语文 科目,13 场次,120 时长union all select 英语,16,100union all select 数学,16,100union all select 物理,8,60union all select 道法,8,60union all select 历史,8,50union all select 化学,8,50
),老师 as (select 语文 科目,4 numunion all select 数学,5union all select 英语,6union all select 物理,3union all select 道法,3union all select 历史,3union all select 生物,3union all select 体育,2
),teacher as (select 科目,numberfrom 老师cross apply (select number from master..spt_valueswhere typep and number between 1 and num) b
),exam as (select *,row_number() over(order by 时长 desc) sn from 考试
),sc as (select * from exam across apply (select convert(nvarchar(max),stuff((select ; 科目 : convert(varchar,number) : convert(varchar,时长)from (select t.*,时长,e.科目 监考,1 批次,场次,row_number() over(order by 场次 desc,t.科目,number) nidfrom teacher tleft join exam e on t.科目e.科目 and e.sna.sn) awhere nid 场次for xml path()),1,1,)) curr) bcross apply (select convert(nvarchar(max),stuff((select ; 科目 : convert(varchar,number) : convert(varchar,(case when nid 场次 then 时长 else 0 end))from (select t.*,时长,e.科目 监考,1 批次,场次,row_number() over(order by 场次 desc,t.科目,number) nidfrom teacher tleft join exam e on t.科目e.科目 and e.sna.sn) afor xml path()),1,1,)) sc) cwhere a.sn1union allselect 科目,场次,时长,sn,c,z from (select b.*,a.scfrom sc a,exam bwhere a.sn 1 b.sn) across apply (select stuff((select ; tn : tsn : convert(varchar,(case when nid场次 then 用时时长 else 用时 end))from (select *,row_number() over(order by (case when tn科目 then 1 else 0 end),用时,tn,tsn) nid from (select master.dbo.RegexMatch(match,[^:]) tn,master.dbo.RegexMatch(match,(?:)\d(?:)) tsn,convert(int,master.dbo.RegexMatch(match,(?:)\d(?$))) 用时from master.dbo.RegexMatches(sc,([^;:]):(\d):(\d))) a) afor xml path()),1,1,) z) bcross apply (select stuff((select ; tn : tsn : convert(varchar,时长)from (select *,row_number() over(order by (case when tn科目 then 1 else 0 end),用时,tn,tsn) nid from (select master.dbo.RegexMatch(match,[^:]) tn,master.dbo.RegexMatch(match,(?:)\d(?:)) tsn,convert(int,master.dbo.RegexMatch(match,(?:)\d(?$))) 用时from master.dbo.RegexMatches(sc,([^;:]):(\d):(\d))) a) awhere nid场次for xml path()),1,1,) c) c
)select tn 科目,tsn,isnull(语文,0) 语文 ,isnull(数学,0) 数学,isnull(英语,0) 英语,isnull(物理,0) 物理,isnull(道法,0) 道法,isnull(历史,0) 历史,isnull(化学,0) 化学,isnull(语文,0) isnull(数学,0) isnull(英语,0) isnull(物理,0) isnull(道法,0) isnull(历史,0) isnull(化学,0) 总用时
from (select 科目,b.* from sccross apply (select master.dbo.RegexMatch(match,[^:]) tn,master.dbo.RegexMatch(match,(?:)\d(?:)) tsn,convert(int,master.dbo.RegexMatch(match,(?:)\d(?$))) 用时from master.dbo.RegexMatches(curr,([^;:]):(\d):(\d))) b
) a
pivot(max(用时) for 科目 in (语文,英语,数学,物理,道法,历史,化学)) p
order by 1 desc,2
----------------------
科目 tsn 语文 数学 英语 物理 道法 历史 化学 总用时
---- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
语文 1 0 100 100 0 0 50 0 250
语文 2 0 100 100 0 0 0 0 200
语文 3 0 100 100 0 0 0 0 200
语文 4 0 100 100 0 0 0 0 200
英语 1 0 100 0 60 60 0 0 220
英语 2 0 100 0 60 60 0 0 220
英语 3 0 100 0 0 60 0 50 210
英语 4 0 100 0 0 60 0 50 210
英语 5 0 100 0 0 60 0 50 210
英语 6 0 100 0 0 60 0 50 210
物理 1 0 100 100 0 0 50 0 250
物理 2 0 100 100 0 0 50 0 250
物理 3 0 100 100 0 0 50 0 250
体育 1 0 100 100 0 0 50 0 250
体育 2 0 100 100 0 0 50 0 250
数学 1 120 0 0 60 0 0 50 230
数学 2 120 0 0 60 0 0 50 230
数学 3 120 0 0 60 0 50 0 230
数学 4 120 0 0 60 0 50 0 230
数学 5 0 0 100 60 60 0 0 220
生物 1 120 0 100 0 0 0 0 220
生物 2 120 0 0 0 60 0 50 230
生物 3 120 0 0 60 0 0 50 230
历史 1 120 0 100 0 0 0 0 220
历史 2 120 0 100 0 0 0 0 220
历史 3 120 0 100 0 0 0 0 220
道法 1 120 100 0 0 0 0 0 220
道法 2 120 0 100 0 0 0 0 220
道法 3 120 0 100 0 0 0 0 220(29 行受影响)
由于每个递归里使用了两个 cross 所以暂时把 order by newid 去掉了否则两个 cross 里的结果会造成不一致不过结果总算出来了。
小结
这一次老顾也是拼尽全力去搞了次数据库查询指令实现嗯也是闲的。好在经过这一次实现发现了以前很多没有注意到的细节并且最终解决掉了一些限制。
最后 sc cte 表里的 cross 其实可以合并成1个切割数据然后再附加两个 cross 就可以使用 newid() 进行随机排序了不过老顾折腾这半天才实现出来暂时真的没心力去继续搞了。
说实话这个需求用代码来实现其实很简单不管是我已经用 python 实现的还是其他编程语言都不复杂 那怕是 mssql 本身加上流程控制也很简单的。老顾是执拗劲上来了非要用查询指令本身进行实现。大家大可不必学老顾这么拧哦。
