GROUP BY与计数的WHERE子句问题：全表统计后插入前10行

你的原查询里，WHERE id < 10先把范围限定在了前10行数据，之后再做分组统计，所以COUNT(*)自然只能算出这10行里每个分组的数量，而不是全表的分组总数。要实现需求，我们需要先拿到全表每个分组的总记录数，再把前10行的数据和这些统计结果关联起来。

这里有两种靠谱的解决方案，你可以根据自己的数据库版本选择：

方法1：子查询+关联查询（兼容所有主流数据库） #

这种方法先单独统计全表的分组总数，再和前10行的数据做关联，确保每一行都能拿到对应分组的全表计数：

INSERT INTO matchNewCounts(`uniqueidentifier`, `fullName`, `first`, `middle`, `last`, `counter`, `city`, `state`, `cit2`, `state2`, `cnt`)
SELECT 
    mni.`uniqueidentifier`, 
    mni.`fullName`, 
    mni.`first`, 
    mni.`middle`, 
    mni.`last`, 
    mni.`counter`,
    mni.`city`, 
    mni.`state`, 
    mni.`cit2`, 
    mni.`state2`, 
    grp.cnt
FROM matchNewIndex mni
-- 先统计全表每个分组的总记录数
JOIN (
    SELECT 
        `first`, `last`, `city`, `state`, 
        COUNT(*) AS cnt
    FROM matchNewIndex
    GROUP BY `first`, `last`, `city`, `state`
) grp 
ON mni.`first` = grp.`first` 
AND mni.`last` = grp.`last` 
AND mni.`city` = grp.`city` 
AND mni.`state` = grp.`state`
-- 只筛选前10行数据
WHERE mni.id < 10;

方法2：窗口函数（适合支持窗口函数的数据库，如MySQL 8.0+、PostgreSQL等） #

如果你的数据库支持窗口函数，这个方法会更简洁，直接在查询中计算每一行所属分组的全表总数：

INSERT INTO matchNewCounts(`uniqueidentifier`, `fullName`, `first`, `middle`, `last`, `counter`, `city`, `state`, `cit2`, `state2`, `cnt`)
SELECT 
    `uniqueidentifier`, 
    `fullName`, 
    `first`, 
    `middle`, 
    `last`, 
    `counter`,
    `city`, 
    `state`, 
    `cit2`, 
    `state2`, 
    -- 窗口函数按分组统计全表总数
    COUNT(*) OVER (PARTITION BY `first`, `last`, `city`, `state`) AS cnt
FROM matchNewIndex
WHERE id < 10;

注意事项 #

• 确保first、last、city、state这四个字段的组合能准确标识你的分组（如果有重复的分组逻辑，结果会自动对应）
• 如果你的数据库版本较低不支持窗口函数，优先选择第一种关联子查询的方法

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