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字段。
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
你可以在这里找到现场演示