当前位置: 动力学知识库 > 问答 > 编程问答 >

proximity - SQL - find row with values of two columns closest to X and Y

问题描述:

I am building a website on wich people can get some infos based on their weight and height.

How would one structure a query that would give me a row with the two specific values closest to the ones users enter?

I found this on stackoverflow and it was very helpful.

I am trying to accomplish something similar to that only with 2 values insted of 1.

网友答案:

If the weight and height are of equal importance, you can use this (linear)

select top 1 *
from tbl
order by ABS([email protected]) + ABS([email protected])

A better option could be to weigh the differences, on a scale, such as making 0.01m (1cm) of height of equal importance as 1kg. Square both sides as well, so that a deviation of 5cm and 5kg is seen as "closer" than 10cm and 0kg.

(Assuming all inputs are in kg and metres)

select top 1 *
from tbl
order by ABS([email protected])^2 + (ABS([email protected])*100)^2
网友答案:

Rather than trying to do some clever SQL tricks to accomodate this, why not restrict user input (using drop down lists, rounding to nearest value, etc.) to match the values available in your dataset.

网友答案:

What about the averaged difference of the two? I would recommend giving the difference in height a higher weight since 6" of difference is much more drastic than 6 lbs of difference. Example:

SELECT * FROM table
ORDER BY ABS(weight - @weight) + ABS(height - @height)*3

This is just me taking a swing at trying to make the difference in height more important. In this example I am giving it a weight of 3. This example assumes a person's height is in inches... If you have it in centimeters or something it might work better.

分享给朋友:
您可能感兴趣的文章:
随机阅读: