mySQL :基于用户表单填充首选项的查询
mySQL :基于用户表单填充首选项的查询
提问于 2014-11-18 17:39:07
答案可能已经有了，所以，我正在寻找我的搜索指南(我不知道是什么时候的情况，还是通过.)
我有一个平日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)。
提前感谢你为我指明了正确的方向。
回答 1
Stack Overflow用户
回答已采纳
发布于 2014-11-18 17:56:26
我会单独运行多个查询。因此，第一个检查所有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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/27000519
复制相关文章
相似问题
腾讯云开发者
