我正在尝试按金额搜索发票。因此,我想搜索所有发票 +/- 搜索金额的 10%,并按最接近给定数字的结果排序:
$search = 100.00
$lower = $search * 0.9; // 90
$higher = $search * 1.1 // 110
$results = $db->select("SELECT ID from `invoices` WHERE Amount >= `$lower` && Amount >= `$higher`");
所以,我不确定如何订购这些。假设此查询给我以下结果:
108, 99, 100, 103, 92
我想对结果进行排序,从搜索的实际数字开始(因为它是完全匹配的),然后从那里开始计算,所以:
100, 99, 103, 92, 108
您可以按如下方式执行此操作:
$search = 100.00
$deviation = 0.10;
$results = $db->select("
SELECT ID, Amount, ABS(1 - Amount/$search) deviation
FROM invoices
WHERE ABS(1 - Amount/$search) <= $deviation
ORDER BY ABS(1 - Amount/$search)
");
输出为:
+----+--------+-----------+
| id | Amount | deviation |
+----+--------+-----------+
| 3 | 100 | 0 |
| 2 | 99 | 0.01 |
| 4 | 103 | 0.03 |
| 1 | 108 | 0.08 |
| 5 | 92 | 0.08 |
+----+--------+-----------+
这是一个SQL小提琴
这样,您就可以让 SQL 通过将实际金额除以"完美"金额 ($search) 来计算偏差。这将是 1 表示完美匹配。通过从 1 中减去此值,完美匹配由值 0 表示。任何偏差都不是零。通过取其绝对值,您可以得到确切的偏差作为小数(代表百分比),例如 0.02(即 2%)。
通过将此偏差与给定的最大偏差($deviation)进行比较,您可以得到所需的。当然,根据这个计算出的偏差,可以很容易地完成排序。
试试这个:
$search = 100.00
$lower = $search * 0.9; // 90
$higher = $search * 1.1 // 110
$results = $db->select("SELECT ID from `invoices`
WHERE Amount >= `$lower` && Amount <= `$higher`
ORDER BY ABS(Amount - $search)
");
ABS 函数返回其参数的绝对值(=>它基本上从负数中删除了减号)。因此,ABS(Amount - $search)
返回与$search
值的距离。
除此之外,您应该考虑使用预准备语句。否则,您的应用程序可能容易受到 sql 注入的攻击。