MariaDB中不使用ORDER BY查询列最低频次值的SQL实现问题

解决方案 #

可通过嵌套子查询结合MIN()聚合函数实现，完全不需要用到ORDER BY和LIMIT关键字，SQL语句如下：

SELECT lvl
FROM enrl
WHERE ayr = '2000/01' 
  AND pid = 'G500'
GROUP BY lvl
HAVING COUNT(*) = (
    -- 内层查询拿到所有符合条件层级的最小就读人数
    SELECT MIN(lvl_stu_count)
    FROM (
        -- 最内层统计每个层级的就读人数
        SELECT COUNT(*) AS lvl_stu_count
        FROM enrl
        WHERE ayr = '2000/01' 
          AND pid = 'G500'
        GROUP BY lvl
    ) AS lvl_count_stat
);

实现逻辑

• 最内层子查询先过滤符合学年、项目ID的记录，按层级分组统计每个层级的就读人数
• 第二层子查询用MIN()聚合函数直接拿到所有层级中的最小就读人数
• 外层查询同样按层级分组统计人数后，通过HAVING子句筛选出人数等于最小人数的层级

补充说明

如果存在多个层级就读人数相同且均为最小值的情况，该语句会返回所有符合要求的层级，比ORDER BY + LIMIT的方案更贴合实际业务需求。
如果你的数据库支持窗口函数，且规则允许窗口函数内使用ORDER BY（一般禁止的是顶层查询的排序子句），也可以用如下写法：

SELECT DISTINCT lvl
FROM (
    SELECT 
        lvl,
        RANK() OVER(ORDER BY COUNT(*)) AS rk
    FROM enrl
    WHERE ayr = '2000/01' 
      AND pid = 'G500'
    GROUP BY lvl
) AS t
WHERE rk = 1;

内容的提问来源于stack exchange，提问作者Lori9