MySQL如何选择最接近我的变量的值 [英] MySQL how to select the nearest values to my variables

问题描述

我有以下query，其中，我试图在一定的呼吸空间内检索输入变量的匹配项.

SELECT fthg, ftag, avover, avunder, whh, wha, whd 
FROM full 
WHERE (whh < ($home_odds + 0.05) 
    AND whh > ($home_odds - 0.05) 
    AND wha < ($away_odds + 0.05) 
    AND wha > ($away_odds -0.05) 
    AND whd < ($draw_odds + 0.05) 
    AND whd > ($draw_odds - 0.05))

在某些情况下，它返回0结果，因此在这种情况下，我想检索与这三个记录最接近的匹配记录，但我不太确定如何将查询组合在一起.

基本上，如果另一个查询不返回结果，这是最后的选择，无论原始值有多远，此查询都会返回次佳的结果.

感谢您的帮助

解决方案

您的原始查询将更简单，更易读，如下所示:

SELECT
    fthg,
    ftag,
    avover,
    avunder,
    whh,
    wha,
    whd
FROM full
WHERE ABS($home_odds - whh) < 0.05
    and ABS($away_odds - wha) < 0.05
    and ABS($draw_odds - whd) < 0.05

如果该查询未返回任何内容，则可以运行此查询:

SELECT
    fthg,
    ftag,
    avover,
    avunder,
    whh,
    wha,
    whd
FROM full
ORDER BY
    ABS($home_odds - whh) + ABS($away_odds - wha) + ABS($draw_odds - whd)
LIMIT 1

它将返回与这三对字段的组合的偏差最小的行.

I have the following query where Im trying to retrieve matches, within a certain breathing space, of the variables entered.

SELECT fthg, ftag, avover, avunder, whh, wha, whd 
FROM full 
WHERE (whh < ($home_odds + 0.05) 
    AND whh > ($home_odds - 0.05) 
    AND wha < ($away_odds + 0.05) 
    AND wha > ($away_odds -0.05) 
    AND whd < ($draw_odds + 0.05) 
    AND whd > ($draw_odds - 0.05))

There are occasions where this returns 0 results so in that case I would like to retrieve the closest matching record to all three but Im not quite sure how to put the query together.

Basically this is the last resort if the other query doesn't return results, this one will return the next best thing no matter how far from the original values.

Thanks for the help

解决方案

Your original query would be simpler and more readable as this:

SELECT
    fthg,
    ftag,
    avover,
    avunder,
    whh,
    wha,
    whd
FROM full
WHERE ABS($home_odds - whh) < 0.05
    and ABS($away_odds - wha) < 0.05
    and ABS($draw_odds - whd) < 0.05

If that query returns nothing, you could run this one:

SELECT
    fthg,
    ftag,
    avover,
    avunder,
    whh,
    wha,
    whd
FROM full
ORDER BY
    ABS($home_odds - whh) + ABS($away_odds - wha) + ABS($draw_odds - whd)
LIMIT 1

It will return the row with the lowest deviation from the combination of those three pairs of fields.

这篇关于MySQL如何选择最接近我的变量的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！