SQL查询以查找多个范围内的值 [英] SQL query for finding a value in multiple ranges

问题描述

我有包含事件日期和3个范围的MySQL数据库，即from1-to1，from2-to2，from3-to3
每个范围都有不同的价格，即from1-to1 rate1，from2-to2 rate2，...

i have MySQL db that contains event's date and 3 ranges, i.e from1-to1, from2-to2, from3-to3
each range has different price, i.e from1-to1 rate1 , from2-to2 rate2, ...

所以每个范围有3列:从，到和评分.

so that's 3 columns for each range: from, to and rate.

我正在尝试查找返回给定月份汇率的查询，这意味着找到该月份所处的范围并返回该范围的汇率.

i'm trying to find the query that returns the rate for a given month, meaning finds the range that the month is in and returns the rate of that range.

有什么想法吗?
谢谢！

any ideas?
thanks!

推荐答案

似乎您的数据模型未标准化.您应该考虑有关创建其他表的建议.

It seems like your data model is not normalized. You should consider morjas suggestion about creating an additional table.

下面是一个非常丑陋的查询，它检查日期是否在三个范围内，然后返回匹配率.

Below is a really ugly query that checks whether a date is in any of the three ranges, and then returns the matching rate.

select case 
        when date '2010-12-05' between range1_from and range1_to then range1_rate
        when date '2010-12-05' between range2_from and range2_to then range2_rate
        when date '2010-12-05' between range3_from and range3_to then range3_rate
       end as rate
  from events
 where date '2010-12-05' between range1_from and range1_to
    or date '2010-12-05' between range2_from and range2_to
    or date '2010-12-05' between range3_from and range3_to;

这篇关于SQL查询以查找多个范围内的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！