MySQL按autfis_id分组取首条并统计同组行数的SQL实现

适用MySQL 8.0+版本（支持窗口函数） #

通过窗口函数直接实现分组排序和计数：

WITH ranked_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY autfis_id ORDER BY id ASC) AS row_rank,
        COUNT(*) OVER (PARTITION BY autfis_id) AS same_autfis_row_count
    FROM tableA
)
SELECT 
    id,
    autfis_id,
    same_autfis_row_count AS `count(rows with same autfis_id)`,
    name,
    col1,
    col2,
    -- 按照实际表结构补全剩余列
    coln
FROM ranked_data
WHERE row_rank = 1;

适用MySQL 5.x及更低版本 #

通过子查询先取每个分组的最小ID和行数，再关联原表取其余字段：

SELECT 
    t.min_id AS id,
    t.autfis_id,
    t.group_count AS `count(rows with same autfis_id)`,
    a.name,
    a.col1,
    a.col2,
    -- 按照实际表结构补全剩余列
    a.coln
FROM (
    SELECT 
        MIN(id) AS min_id,
        autfis_id,
        COUNT(*) AS group_count
    FROM tableA
    GROUP BY autfis_id
) t
INNER JOIN tableA a ON t.min_id = a.id;

两种方案都会返回每个autfis_id对应最早出现的ID行，同时携带该分组的总行数，其余字段均取最早ID对应行的数值，完全符合需求。

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