查找两个值之间的数字，并返回对应于较低值的字段 [英] Find a number that is between two values and return field corresponding to lower value

问题描述

另一个新手问题-我很感谢大家在这里提供的帮助，我将向其他人表示感谢.这是我要处理的内容:

Another newbie question - I appreciate how everyone is so helpful here and I will return the favor for others. Here's what I am dealing with:

我有一张桌子 tblAssignment -这只是一个更大桌子的样本，但我希望它很简单，以便我可以正确地传达给您，然后理解所有即将提出的答案的逻辑:

I have a table tblAssignment - this is just a sample of a bigger table, but I want it to be simple so I can convey it to you properly and then understand the logic behind any forthcoming answers:

+---------+-------------+-------------------+
| Lesson  | SkillNeeded |  EmployeeAssigned |
+---------+-------------+-------------------+
| Math    |           0 |  Mike             |
| Math    |           2 | Jason             |
| Math    |           5 | Derek             |
| Math    |           9 | John              |
| Math    |          13 | Brian             |
+---------+-------------+-------------------+

因此，我将具有所需的技能编号，在此示例中，使用数字4.我需要在表中( SkillNeeded 字段下)对该值进行Access查找，然后希望它在 EmployeeAssigned 字段下建议一个名称.数字为4时，该数字介于德里克和杰森之间，后者分别为2和5.我想要返回的是与我的号码介于其中的最低 SkillNeeded 号码相关的名称；因此，在此示例中，将是Jason.

So, I would have a desired skill number, and for this example, let's use the number 4. I need to have Access lookup that value in the table (under the SkillNeeded field) and I'd want it to suggest one name under the EmployeeAssigned field. With a 4, that number falls between Derek and Jason, who have a 2 and a 5, respectively. What I'd like returned is the the name that is associated with the lowest SkillNeeded number of which my number is between; so, in this example, it would be Jason.

如果我的电话号码是8，则查询将返回Derek，因为8将在5到9之间，并且将使用与两者中较低的 SkillNeeded 号码相关联的名称，即5和德里克.

Had my number been 8, the query would return Derek, because 8 would be between 5 and 9 and it would take the name associated with the lower SkillNeeded number of the two, which would be 5 and Derek.

我希望这是有道理的.再次，所有帮助将不胜感激-预先感谢您！

I hope that made sense. Again, any and all help will be truly appreciated - thank you in advance!

-乔什

推荐答案

尝试以下查询:

Select TOP 1* from tblAssignment
   group by SkillNeeded 
            having SkillNeeded <= 4
                  order by desc;

希望有帮助！

这篇关于查找两个值之间的数字，并返回对应于较低值的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！