如何从数据库中查找范围值 [英] How to lookup range value from database

问题描述

如何根据数据库中存储的范围查找值.

How to find a value based on a range stored in the database.

我的friend_levels表:

并在我的profile桌子上:

当我拥有friend_points = 1时，我将获得正常费率，然后，如果拥有friend_points = 180，我将获得好朋友，因此在编程时基本上就像这样

When I have friend_points = 1, I will get Normal Rate, then if I had friend_points = 180 I will get Great Friend, so it's basically like this on programming

if($profile_points >= 0 && $profile_points < 50) {
    return 'Normal Rate';
} else if($profile_points >= 50 && $profile_points < 100) {
    return 'Friend';
} else if($profile_points >= 100 && $profile_points < 150) {
    return 'Good Friend';
}....

我的问题也是可以在"QUERY"上进行吗?还是我只是在PHP上做到了?

my question too is does it possible on QUERY? or I just make it on the PHP?

EDIT1 :是否可以获取下一个目标值? 对于前.如果我在Friend rate with 68 points上，如何获取100 = Good Friend?没关系，我只想得到下一行.

EDIT1: Is there a way to get the next target value? For ex. If I'm on the Friend rate with 68 points how to get the 100 = Good Friend ? nevermind the substraction, I just want to get the next row.

推荐答案

如果我对您的理解正确，则可以使用CASE EXPRESSION这样的方式:

If I understood you correctly, you can use CASE EXPRESSION like this:

SELECT id,user_id,
       case when friend_points between 0 and 49 then 'Normal rate'
            when friend_points between 50 and 99 then 'Friend'
            when friend_points between 100 and 149 then 'Good friend'
            .......
       end as 'Friend_Status'
FROM profile

或者，如果此名称可以动态更改，则可以使用联接:

Or, if this names can change dynamicly then with a join:

SELECT t.id,t.user_id,s.name
FROM profile t
INNER JOIN friend_levels s ON(t.friend_points >= s.points_needed)
WHERE s.points_needed = (select min(f.points_needed)
                         from friend_levels f
                         where t.friend_points >= f.points_needed)

这篇关于如何从数据库中查找范围值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！