mysql最大值相同的记录_mysql-返回不同记录的条件最小最大值

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的分段记录意味着它是一个区域。

我想按地名搜索记录以建立自动完成功能。

如果记录具有相同的名称,如果其中一个记录是一个区域,即具有特征代码RGN,那么我只想返回

否则,我想返回与具有最低id的名称匹配的记录。

我试过以下方法,但不起作用:

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