Блог

У меня есть дамп базы данных с веб-сайта geonames для Великобритании. Он состоит примерно из 60000 записей. пример данных следующий:

 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
Запись secord с функциональным кодом RGN означает, что это регион.

Я хочу выполнить поиск записей по названиям мест, чтобы создать функцию автозаполнения. Если записи имеют одинаковое имя, и если одна из этих записей является регионом, т. Е. имеет feature_code RGN, то я хочу вернуть только эту запись, в противном случае я хочу вернуть запись, которая соответствует этому имени с наименьшим идентификатором.

Я пробовал следующее, но это не работает:

    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
  

Я думаю, что условная агрегация должна сработать. Вы можете фильтровать записи по name , а затем применять логику в агрегатных функциях. Если запись существует с feature_code = 'RGN' , то вы хотите выбрать ее, иначе вы бы выбрали минимум id в соответствующей записи.

 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 |
  

ПРИМЕЧАНИЕ: если вам нужна вся соответствующая запись, одним из решений является просто JOIN приведенный выше результирующий набор с исходной таблицей:

 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;
  

ДЕМОНСТРАЦИЯ:

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

В MySQL вы можете использовать коррелированный подзапрос:

 select l.*
from locations l
where l.id = (select l2.id
              from locations l2
              where l2.name = l.name
              order by (feature_code = 'RGN') desc,  -- put regions first
                       id asc
             );
  

В MySQL 8 вы также можете использовать row_number() :

 select l.*
from (select l.*,
             row_number() over (partition by name 
                                order by (feature_code = 'RGN') desc, id
                               ) as seqnum
      from locations l
     ) l
where seqnum = 1;
  

один подход может быть exists и объединить все

 select t1.* from location t1
where exists ( select 1 from location t2 where t2.name=t1.name and t2.feature_code='RGN'
             )
 and t1.feature_code='RGN'
union all

select t1.* from location t1
where not exists ( select 1 from location t2 where t2.name=t1.name and 
                t2.feature_code='RGN'
                  )
  and t1.id=(select min(id) from location t2 where t2.name=t1.name)