多值查询以查找最近匹配的行[关闭]

Question

I'm looking to create a query that returns the nearest matching row for a set of values. The values are typically between 1-3 (one is 1-5).

I'm not sure the best way to approach right this script. I've had some people say that using distance is the way, and I've had others say that using where/or is the way. Basically the result doesn't have to be an exact match, it just has to list the results in rank of closeness.

The form on this page here is basically what I'm trying to replicate. If anyone could shed light on the approach to the query that I should take that would be very much appreciated.

Answer 1

Generate a calculated overall "distance" by getting sum the absolute value of the selected number minus the column value for all columns.

SELECT id, ABS(5-col1)+ABS(5-col2)+ABS(5-col3) as distance
FROM sometable
ORDER BY distance ASC

where 5 is the value you are comparing against.