答案
可能已经在SO中可用,我正在寻找搜索指南(我不知道这是何时或订购方式...
我有一个工作日的BD,充满了可用的时间表:
+____+__________+________+________+_________+___________+__________+________+
| id | Saturday | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday |
+____+__________+________+________+_________+___________+__________+________+
| 1 | OFF | OFF | 8-4 | 8-4 | 8-4 | 8-4 | 4-0 |
| 2 | 4-0 | OFF | OFF | 8-4 | 8-4 | 8-4 | 8-4 |
| 3 | 4-0 | OFF | OFF | 8-4 | 8-4 | 8-4 | 4-0 |
-----------------------------------------------------------------------------
用户将填写具有优先级的计划首选项表单:
Priority 1: Saturday = OFF
Priority 2: sunday = OFF
Priority 3: Monday = 8-4
Priority 4: Tuesday= 8-4
Priority 5: Wednesday = 8-4
Priority 6: Thursday = 8-4
Priority 7: Friday = 8-4
现在,如果我不能匹配所有 7 个优先级,我需要检查我是否可以满足他的前 6 个优先级,如果我不能满足他的前 6 个优先级,我会尝试满足他的前 5 个优先级,依此类推,直到找到匹配项。
在上面的例子中,我无法满足优先级 7,因此最佳匹配将是计划 ID 1(匹配优先级 1 到 6)。
提前感谢您为我指明正确的方向。
我会分别运行多个查询。因此,第一个检查所有 n 个属性的匹配项,随后检查 n-1、n-2 ...优先 级。你得到的第一场比赛将是最好的比赛。整体逻辑得到简化,性能影响应该不大
或者,如果您绝对喜欢一种查询方法,则可以编写一个查询构建循环,该循环将为您构建以下内容:
select 1 as priority, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday FROM weekdays AS all_priorities WHERE {Priority1} AND {Priority2} AND {Priority3} AND{Priority4} AND{Priority5} AND {Priority6} AND {Priority7}
UNION
select 2 as priority, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday FROM weekdays AS all_priorities WHERE {Priority1} AND {Priority2} AND {Priority3} AND {Priority4} AND{Priority5} AND {Priority6}
UNION
select 3 as priority, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday FROM weekdays AS all_priorities WHERE {Priority1} AND {Priority2} AND {Priority3} AND{Priority4} AND {Priority5}
UNION
select 4 as priority, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday FROM weekdays AS all_priorities WHERE {Priority1} AND {Priority2} AND {Priority3} AND {Priority4}
UNION
select 5 as priority, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday FROM weekdays AS all_priorities WHERE {Priority1} AND{Priority2} AND {Priority3}
UNION
select 6 as priority, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday FROM weekdays AS all_priorities WHERE {Priority1} AND{Priority2}
UNION
select 7 as priority, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday FROM weekdays AS all_priorities WHERE {Priority1}
ORDER BY priority DESC