sql - MySQL - 使用 MIN 分组未按预期工作

Question

我有以下联合查询，我正在设置一个虚拟行号：

SELECT @row_num:=2 as row_num, id, name, admin1, admin3, admin4
  FROM locations
 WHERE feature_class IN ('P', 'A')
   AND name LIKE 'cornwall%'
UNION ALL      
SELECT @row_num:=1 as row_num, id, name, admin1, admin3, admin4
 FROM locations
WHERE feature_class = 'L' AND feature_code = 'RGN'
  AND name LIKE 'cornwall%'

这将返回以下结果：

row_num   |   id       |     name    |   admin1   |   admin3   |  admin4  
-------------------------------------------------------------------------
   2      |   2652355  |   Cornwall  |   ENG      |            |          
   1      |   11609029 |   Cornwall  |   ENG      |            |         

现在，当我将此查询添加为子查询并使用 MIN 选择具有最低 row_num 的行时，我得到不正确的结果：

 SELECT MIN(t0.row_num), 
   t0.id,
   t0.name, 
   t4.name AS town_admin4,
   t3.name AS county_admin3, 
   t1.name AS admin1
  FROM (
           SELECT @row_num:=2 as row_num, id, name, admin1, admin3, admin4
            FROM locations
           WHERE feature_class IN ('P', 'A')
             AND name LIKE 'cornwall%'
           UNION ALL       
           SELECT @row_num:=1 as row_num , id, name, admin1, admin3, admin4
            FROM locations
           WHERE feature_class = 'L' AND feature_code = 'RGN'
             AND name LIKE 'cornwall%'
      ) t0
  LEFT JOIN locations t1 ON t1.admin1 = t0.admin1 AND t1.feature_code = 'ADM1'
  LEFT JOIN locations t3 ON t3.admin3 = t0.admin3 AND t3.feature_code = 'ADM3'
  LEFT JOIN locations t4 ON t4.admin4 = t0.admin4 AND t4.feature_code = 'ADM4'
  GROUP BY t0.name,
           t4.name,
           t3.name, 
           t1.name
  ORDER BY t0.name  

我得到以下结果：

MIN(t0.row_num)   |   id       |     name    |   town_admin4   |   county_admin3   |  admin1  
--------------------------------------------------------------------------------------------
       1          |   2652355  |   Cornwall  |                 |                   |  England   

如您所见，上述内容是不正确的。第 1 行应记录 id 为 11609029

为什么会这样 - 为什么 MIN 运算符没有按预期工作？

标签： sqlgroup-bymysql-5.7