对于SQL中最后一个表中的每个元素,如何返回表中的实体的值,该值小于但最接近另一个表中的值? [英] How do I return a value of an entity in a table that is less than but closest to the value in another table for each element in the last table in SQL?
问题描述
我在MS Access中有两个表,我试图为其中一个表添加一个字段,以告诉另一个表中的哪个记录的值小于第一个字段的值,但最接近?到目前为止,我有这个查询(只是一个select语句来测试输出,而不更改现有表),但是它列出了所有小于查询值的值:
I have two tables in MS Access and I am trying to add a field for one of those tables that tells which record from another table has a value that is less than the first field's value, but comes the closest? I have this query so far (just a select statement to test output and not alter existing tables), but it lists all values that are less than the querying value:
SELECT JavaClassFileList.ClassFile, ModuleList.Module
FROM JavaClassFileList, ModuleList
WHERE ModuleList.Order<JavaClassFileList.Order;`
我尝试使用类似SELECT JavaClassFileList.Classfile, MAX(ModuleList.Module)
的方法,该方法仅显示最大模块,但将其与上述select语句结合使用,但是它会说它只会返回一条记录.
I tried using things likeSELECT JavaClassFileList.Classfile, MAX(ModuleList.Module)
, which will only display the maximum module but combined it with the select statement above, but it would say that it would only return one record.
期望的输出:我有一些记录a,b和c,我将它们称为存储每个记录的各种信息,而a将一列的值存储为732,而b将一列的值存储为731.同一列. c存储的是720的值.在另一个表中,d存储的是730的值,e存储的是718的值.我想要这样的输出(它们按从大到小的顺序排列):
Output desired: I have some records, a, b, and c, I shall call them, each storing various information, while a is storing a value of 732 in a column, and b is storing a value of 731 in the same column. c is storing a value of 720. In another table, d is storing a value of 730 and e is storing a value of 718. I want the output like this (they are ordered largest to smallest):
- a 732 d 730
- b 731 d 730
- c 720 e 718
右边可以有重复项,而左边则没有重复项.我怎么能得到这个结果?
There can be duplicates on the right, but no duplicates on the left. How can I get this result?
推荐答案
我将使用相关子查询来处理这种类型的查询.我认为Access中有以下几句话:
I would approach this type of query using a correlated subquery. I think the following words in Access:
SELECT jc.ClassFile,
(select top 1 ml.Module
from ModuleList as ml
where ml.[Order] < jc.[Order]
)
FROM JavaClassFileList as jc;
这篇关于对于SQL中最后一个表中的每个元素,如何返回表中的实体的值,该值小于但最接近另一个表中的值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!