综合技术

Obtain values ​​between ranges

微信扫一扫,分享到朋友圈

Obtain values ​​between ranges
0

The more I think of it the more I am confused, could be because it is quite a while that i wrote some complex sql.

I have a table that has a range for a value. Lets call it a range:

RANGE
RANGE_ID   RANGE_SEQ   MIN   MAX  FACTOR
       1           1     0    10       1
       1           2    11   100     1.5
       1           3   101           2.5
       2           1     0    18       1
       2           2    19             2

And I have anothe table that uses these ranges. Lets call it application

APPLICATION
APP_ID   RAW_VALUE  RANGE_ID   FINAL_VALUE
     1         20.0       1          30.0     /*In Range 1, 20 falls between 11 and 100, so 1.5 is applied)*/
     2         25.0       2          50.0
     3         18.5       2          18.5

I want to get those RAW_VALUES
that fall between the ranges. So for range 2, I want those APP_ID
s that have a RAW_VALUE
between 18 and 19. Similarly for range 1, I want those APP_ID
s that have a RAW_VALUE
between 10 and 11 and 100 and 101.

I want to know whether this is possible with SQL, and some pointers on what I can try. I don’t need the sql itself, just some pointers to the approach.

Try this to get you close

select app_id,raw_value,aa.range_id,raw_value * xx.factor as FinaL_Value
from Application_table  aa
join range_table xx on (aa.raw_value between xx.min and xx.max)
                  and (aa.range_id=xx.range_id)

To get non-matches (i.e. raw_values that do not exist in the table), try this

select app_id,raw_value,aa.range_id
from Application_table  aa
left join range_table xx on (aa.raw_value between xx.min and xx.max)
                  and (aa.range_id=xx.range_id)
where xx.range_id is null

阅读原文...


微信扫一扫,分享到朋友圈

Obtain values ​​between ranges
0

Hello, buddy!

Twitter Provides a New Overview of 'State-Backed' Information Operations on its Platform

上一篇

ArrayList 深度挖掘

下一篇

评论已经被关闭。

插入图片

热门分类

往期推荐

Obtain values ​​between ranges

长按储存图像,分享给朋友