MySQL-有条件的MIN MAX返回不同的记录 [英] MySQL - Conditional MIN MAX to return distinct record

问题描述

我有一个数据库转储，来自英国的地名网站。它包含大约60000条记录。
示例数据如下：

I have a database dump from the geonames website for Great Britain. It consists of approx 60000 records. example data is as follows:

id       |     name    |   admin1   |   admin2   |  admin3  |  feature_class  |  feature_code
-------------------------------------------------------------------------------------------
2652355  |   Cornwall  |   ENG      |     C6     |          |      A          |    ADM2
11609029 |   Cornwall  |   ENG      |            |          |      L          |    RGN
6269131  |   England   |   ENG      |            |          |      A          |    ADM1

带有功能代码ADM2的第一条记录表示它是管理级别2
第二条记录功能代码为RGN意味着它是一个区域。

The first record with feature code ADM2 means it is administrative level 2 The secord record with feature code RGN means it is a region.

我想按地名搜索记录以构建自动完成功能。
其中记录具有相同的名称，并且如果其中一个记录是一个区域，即具有feature_code RGN，那么我只想返回该
记录，否则我想返回与该名称具有最低名称的记录匹配的记录

I want to search for records by place names to build an autocomplete feature. Where records have the same name and if one of those records is a region i.e. has feature_code RGN then I want to return only that record otherwise I want to return the record which matches that name that has the lowest id.

我尝试了以下操作，但不起作用：

I have tried the following but it doesn't work:

   SELECT IF(t0.feature_code = 'RGN', MAX(t0.id), MIN(t0.id)) as id
       , CONCAT_WS(', ', t0.name,
                  IF(t3.name != t0.name, t3.name, NULL),
                  IF(t2.name != t0.name, t2.name, NULL),
                  IF(t1.name != t0.name, t1.name, NULL)) AS name
     FROM locations t0
  LEFT JOIN locations t1 ON t1.admin1 = t0.admin1 AND t1.feature_code = 'ADM1'
  LEFT JOIN locations t2 ON t2.admin2 = t0.admin2 AND t2.feature_code = 'ADM2'
  LEFT JOIN locations t3 ON t3.admin3 = t0.admin3 AND t3.feature_code = 'ADM3'
  WHERE 
      (t0.feature_class IN ('P', 'A') OR (t0.feature_class = 'L' AND t0.feature_code = 'RGN' ) )
      AND t0.name like 'Cornwall%' 
  GROUP BY CONCAT_WS(', ', t0.name,
                     IF(t3.name != t0.name, t3.name, NULL),
                     IF(t2.name != t0.name, t2.name, NULL),
                     IF(t1.name != t0.name, t1.name, NULL))
  ORDER BY t0.name 

返回错误记录：

id      | name
---------------------------
2652355 | Cornwall, England

推荐答案

我认为条件聚合应该可以解决问题。您可以按名称过滤记录，然后将逻辑应用于聚合函数。如果存在具有 feature_code ='RGN'的记录，则要选择该记录，否则将选择最小的 id

I think that conditional aggregation should do the trick. You can filter records by name, then apply the logic within aggregate functions. If a record exists with feature_code = 'RGN' then you want to select it, else you would pick the minimum id in matching record.

SELECT IFNULL(MAX(CASE WHEN feature_code = 'RGN' THEN id END), MIN(id)) id_found
FROM mytable
WHERE name = @name;

DB Fiddle上的演示 ，当搜索'Cornwall'：

| id_found |
| -------- |
| 11609029 |

NB：如果想要整个匹配记录，一种解决方案是简单地将以上结果集与原始表一起加入：

NB : if you want the whole matching record, one solution is to simply JOIN the above resultset with the original table:

SELECT t.*
FROM mytable t
INNER JOIN (
    SELECT IFNULL(MAX(CASE WHEN feature_code = 'RGN' THEN id END), MIN(id)) id_found
    FROM mytable
    WHERE name = @name
) x ON x.id_found = t.id;

演示 ：

Demo:

| id       | name     | admin1 | admin2 | admin3 | feature_class | feature_code |
| -------- | -------- | ------ | ------ | ------ | ------------- | ------------ |
| 11609029 | Cornwall | ENG    |        |        | L             | RGN          |

这篇关于MySQL-有条件的MIN MAX返回不同的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！