如何在PHP中找到最接近的值

时间:2021-12-12 01:43:32

How to determine the RESULTS field in table users, base on USER SCORE field with the provisions of the value closest to SCORE BRAND field.

如何确定表用户中的RESULTS字段,基于USER SCORE字段,其值最接近SCORE BRAND字段。

如何在PHP中找到最接近的值

This is table Brand

这是表品牌

    <table>
<tr>
<th>BRAND NAME</th>
<th>SCORE BRAND</th>
</tr>";
$sql = mysql_query("SELECT * FROM brand");
while($m=mysql_fetch_array($sql)){ 
echo "<tr> 
<td>$m[brand_name]</td>
<td>$m[score]</td><tr>";
}
</table>

This is table users

这是表用户

    <table>
<tr>
<th>USER NAME</th>
<th>SCORE USER</th>
<th>RESULT</th>
</tr>";
$sql2 = mysql_query("SELECT * FROM users");
while($u=mysql_fetch_array($sql2)){ 
echo "<tr> 
<td>$u[username]</td>
<td>$u[score]</td>
<td> ??? </td>
<tr>";
}
</table>

4 个解决方案

#1


3  

You can use subquery in selection to find proper brand for every selected user like this:

您可以在选择中使用子查询为每个选定的用户找到合适的品牌,如下所示:

SELECT u.*, (
    SELECT b.id
    FROM brand AS b
    ORDER BY ABS(b.score - u.score) ASC, b.score DESC -- selects brands ordered by their difference from user's score
    LIMIT 1 -- get just the first brand (with score closest to user's)
)
FROM user AS u

#2


0  

select * 
from table 
order by abs(value - $myvalue)
limit 1

#3


0  

You should have some pre-defined threshold to match around (so that score 1 would not match with score 1000).

您应该有一些预定义的阈值来匹配(因此得分1与得分1000不匹配)。

SELECT .. WHERE score_brand >= score_user - :epsilon AND score_brand <= score_user + :epsilon

#4


0  

Finally I got some pretty solution:

最后,我得到了一些漂亮的解

SELECT u.*, b.nama_lengkap as result 
FROM `users` AS u 
JOIN `brands` AS b 
WHERE (b.score - u.score) > 0 
AND (b.score - u.score) < 10 
ORDER BY u.id;

b.nama_lengkap -> brand name

b.nama_lengkap - >品牌名称

I have just joined two tables and did some arithmetic operation to get the appropriate row.

我刚刚加入了两个表并进行了一些算术运算以获得适当的行。

EDIT

SELECT v.id, v.username, v.score, b.nama_lengkap AS result
FROM (SELECT users.id, users.score, users.username, min(brands.score) nextSc
      FROM users
      LEFT JOIN brands
        ON users.score <= brands.score
     GROUP BY users.id, users.score) AS v
LEFT JOIN brands b
  ON v.nextSc = b.score
ORDER BY v.id, v.score;

This is the complete dynamic query for any multiplicated values. It will get you closure score of brands for users.

这是任何多重值的完整动态查询。它将为您提供用户的品牌关闭分数。

You can find LIVE DEMO HERE

你可以在这里找到现场演示

#1


3  

You can use subquery in selection to find proper brand for every selected user like this:

您可以在选择中使用子查询为每个选定的用户找到合适的品牌,如下所示:

SELECT u.*, (
    SELECT b.id
    FROM brand AS b
    ORDER BY ABS(b.score - u.score) ASC, b.score DESC -- selects brands ordered by their difference from user's score
    LIMIT 1 -- get just the first brand (with score closest to user's)
)
FROM user AS u

#2


0  

select * 
from table 
order by abs(value - $myvalue)
limit 1

#3


0  

You should have some pre-defined threshold to match around (so that score 1 would not match with score 1000).

您应该有一些预定义的阈值来匹配(因此得分1与得分1000不匹配)。

SELECT .. WHERE score_brand >= score_user - :epsilon AND score_brand <= score_user + :epsilon

#4


0  

Finally I got some pretty solution:

最后,我得到了一些漂亮的解

SELECT u.*, b.nama_lengkap as result 
FROM `users` AS u 
JOIN `brands` AS b 
WHERE (b.score - u.score) > 0 
AND (b.score - u.score) < 10 
ORDER BY u.id;

b.nama_lengkap -> brand name

b.nama_lengkap - >品牌名称

I have just joined two tables and did some arithmetic operation to get the appropriate row.

我刚刚加入了两个表并进行了一些算术运算以获得适当的行。

EDIT

SELECT v.id, v.username, v.score, b.nama_lengkap AS result
FROM (SELECT users.id, users.score, users.username, min(brands.score) nextSc
      FROM users
      LEFT JOIN brands
        ON users.score <= brands.score
     GROUP BY users.id, users.score) AS v
LEFT JOIN brands b
  ON v.nextSc = b.score
ORDER BY v.id, v.score;

This is the complete dynamic query for any multiplicated values. It will get you closure score of brands for users.

这是任何多重值的完整动态查询。它将为您提供用户的品牌关闭分数。

You can find LIVE DEMO HERE

你可以在这里找到现场演示