mysql - MySQL 中的计数或条件限制

Question

我正在制作一个使用地理定位的网络应用程序，并且正在创建一个“附近的地方” View 。这是非常简单的逻辑:它显示了数据库中存储的最近的 5 个位置。完美。

诀窍是我希望它返回最近的 5 个地点或两英里内的所有地点，以较大者为准。换句话说，我希望用户能够至少看到两英里内的所有地方，但如果该半径内没有 5 个地方，我希望他们显示最近的 5 个.

让我们将这些用于示例数据集。第 1 组:

| id | name                 | distance  |
+----+----------------------+-----------+
| 3  | Earl of Sandwich     | 0.3       |
| 4  | Nails 'n More        | 0.8       |
| 22 | City Hotel           | 1.7       |
| 5  | Mighty Medicine      | 2.1       |
| 25 | Wonder Wings         | 2.5       |
| 6  | Jean Warehouse       | 2.7       |
| 9  | Ship Safe & Speedy   | 2.9       |
| 2  | Bagel Bonus          | 4.1       |
+----+----------------------+-----------+

第 2 组:

| id | name                 | distance  |
+----+----------------------+-----------+
| 3  | Earl of Sandwich     | 0.1       |
| 4  | Nails 'n More        | 0.2       |
| 5  | Mighty Medicine      | 0.5       |
| 6  | Jean Warehouse       | 0.7       |
| 9  | Ship Safe & Speedy   | 0.9       |
| 2  | Bagel Bonus          | 1.2       |
| 22 | City Hotel           | 1.7       |
| 25 | Wonder Wings         | 2.1       |
+----+----------------------+-----------+

在第一组中，我想返回第 3、4、22、5 和 25 行。在第二组中，我想显示第 3、4、5、6、9、2 和22.

我知道我总是可以将查询限制在例如 100 个位置，然后在 PHP 中遍历结果集以进行过滤...但我想知道是否有更有效的方法可以在 SQL 中正确执行此操作。

Answer 1

总而言之，执行此操作的方法是同时运行查询并采用集合的 UNION。而已。这样做实际上几乎没有性能损失，因为如果结果中确实有超过 5 行，则已经需要第一组(可以产生 >5 行)。

详细 - 下面是原始答案

为了说明，我没有使用 2 个数据集，而是在同一个示例表中使用了 2 个列，下面进一步显示了查询:

drop table if exists tbl1;
create table tbl1 (
id int,
name varchar(100),
distance1 float,
distance2 float
);
insert tbl1 values
( 3  , 'Earl of Sandwich   ', 0.3, 0.1),
( 4  , 'Nails ''n More     ', 0.8, 0.2),
( 22 , 'City Hotel         ', 1.7, 1.7),
( 5  , 'Mighty Medicine    ', 2.1, 0.5),
( 25 , 'Wonder Wings       ', 2.5, 2.1),
( 6  , 'Jean Warehouse     ', 2.7, 0.7),
( 9  , 'Ship Safe & Speedy ', 2.9, 0.9),
( 2  , 'Bagel Bonus        ', 4.1, 1.2);

以及查询和结果:

/* query 1 */
select id, name, distance1
from (
    select *
    from tbl1
    where distance1 <= 2.0
    order by distance1) a
union
select id, name, distance1
from (
    select *
     from tbl1
    order by distance1
     limit 5) b;

/* result 1 */
id;name;distance1
3;Earl of Sandwich   ;0.3
4;Nails 'n More     ;0.8
22;City Hotel         ;1.7
5;Mighty Medicine    ;2.1
25;Wonder Wings       ;2.5

/* query 2 */
select id, name, distance2
from (
    select *
    from tbl1
    where distance2 <= 2.0
    order by distance2) a
union
select id, name, distance2
from (
    select *
     from tbl1
    order by distance2
     limit 5) b;

/* result 2 */
id;name;distance2
3;Earl of Sandwich   ;0.1
4;Nails 'n More     ;0.2
5;Mighty Medicine    ;0.5
6;Jean Warehouse     ;0.7
9;Ship Safe & Speedy ;0.9
2;Bagel Bonus        ;1.2
22;City Hotel         ;1.7

这个查询的性能已经很好了。
第一个 UNION 部分挑选出 <2km 的部分。这是必需的，因为您需要所有匹配项。
下一部分选择前 5 名并假设您有一个索引，这很容易收集。两个部分的组合(UNION)非常快。