对于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屋！