SQL在两个表之间使用 [英] SQL Using Between for two tables

问题描述

我想根据位于另一个表的两列之间的值从表中获取数据，下面的SQL可以解释我的目的:

Hi I want to fetch data from a Table based on values that lie between two columns of another table, below SQL shoulde explain my purpose:

SELECT * FROM TABLE 1 WHERE 1.FIELD1 BETWEEN 
(SELECT 2.RANGE_FROM FROM TABLE 2) AND (SELECT 2.RANGE_TO FROM TABLE 2)

这在我遇到错误时不起作用:

This is not working as I am getting error:

Error: SQL0811N  The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row.  SQLSTATE=21000
 (State:21000, Native Code: FFFFFCD5)

这很明显，因为两个子查询都返回多行.因此，我想编写一个SQL来执行以上功能而不会出现错误. 平台是IBM DB2.

This is obvious as both the subqueries return multiple rows. So I want to write a SQL to perform above function without error. The platform is IBM DB2.

编辑:

好吧，我认为我通过使用以下条件联接2个表来解决了这一问题:

Ok I think I solved this one by joining 2 tables using the condition:

SELECT * FROM TABLE1 A, TABLE2 B WHERE A.FIELD1 BETWEEN B.RANGE_FROM AND B.RANGE_TO

不过，还需要更多测试.

More testing is required though.

推荐答案

我怀疑您要检查table1列的值是否在table2(某行)的2列之间:

I suspect you want to check if the table1 column values are between the 2 columns of (some row of) table2:

SELECT a.* 
FROM TABLE1 AS a 
WHERE EXISTS 
      ( SELECT 1
        FROM TABLE2 AS b 
        WHERE a.FIELD1 BETWEEN b.RANGE_FROM AND b.RANGE_TO
      ) ;

这样，您将没有table1的重复项，因此不需要DISTINCT.

This way, you'll have no duplicates from table1, so there is no need for DISTINCT.

还请注意条件:

a.FIELD1 BETWEEN b.RANGE_FROM AND b.RANGE_TO

等效于:

b.RANGE_FROM <= a.FIELD1  AND  a.FIELD1 <= b.RANGE_TO

这篇关于SQL在两个表之间使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！