在另一个表的两列中查找范围(箱)中的值的列表，并从第三列中获取相应的值 [英] Look up a list of values in the ranges (bins) as defined by two columns in another table and get the corresponding value from the third column

问题描述

您好，我有两个表T1和T2. T1具有一列整数值. T2具有由两列定义的范围，并且每个范围都有一个对应的值...像这样:

Hello I have two tables T1 and T2. T1 has a column of integer values. And T2 has ranges defined by two columns and a corresponding value for each range... Something like this:

range_min range_max  corr_value
   5         10         1020
   11        15         5000

假设我希望能够从T2获得T1的每个整数的值"，具体取决于整数值所处的范围.假设我在T1中有6、7和12.然后，理想的结果将如下所示:

Suppose I want to be able to get the "value" from T2 for each integer of T1 depending on which range the integer value falls into. Say, I have 6, 7, and 12 in T1. Then, the ideal result would look like this:

integer_val  corr_value
     6          1020
     7          1020
     12         5000

请注意，我没有任何要加入的内容.我不知道如何使用SQL做到这一点.我不知道这是否会有所作为，但我正在使用HiveQL.

Note that I don't have anything to join on. I couldn't figure out how to do this using SQL. I don't know if it'll make a difference, but I am using HiveQL.

非常感谢您的帮助.谢谢！

I really appreciate your help. Thank you!

欢呼

推荐答案

您可以使用非等额联接.如果您的SQL支持between，请使用

You can use a non-equijoin. If your SQL supports between, use

select t1.integer_val, t2.corr_value
from t1
left outer join t2 on t1.integer_val between t2.range_min and t2.range_max

否则，请使用

select t1.integer_val, t2.corr_value
from t1
left outer join t2 on t1.integer_val >= t2.range_min and t1.integer_val <= t2.range_max

此处是对sqlfiddle上的mysql演示的引用.

仅当t1中没有重叠时，此查询才按您描述的方式工作.

This query works as you describe only when there are no overlaps in the t1.

这篇关于在另一个表的两列中查找范围(箱)中的值的列表，并从第三列中获取相应的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！