mysql : Query based on user form filled preference

Question

答案可能已经在 SO 中可用，我正在查看搜索指南(我不知道这是 CASE WHEN 还是 Order BY...)

我有一份工作日 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)。

预先感谢您为我指明了正确的方向。

Answer 1

我会分别运行多个查询。因此，第一个检查所有 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