如何将同acc_no的统计数作为新列生成目标SQL表

方法一：使用窗口函数（推荐，简洁高效） #

直接利用窗口函数COUNT() OVER()，无需额外关联表，就能为每个acc_no的行填充对应的总行数：

SELECT 
    a.acc_no,
    a.id,
    COUNT(*) OVER (PARTITION BY a.acc_no) AS total_num,
    b.status
FROM table1 a
INNER JOIN table2 b
    ON a.sid = b.sid
WHERE b.type = 'SMT'
    AND b.status = 'Active'
ORDER BY a.acc_no, a.sid, a.dt DESC;

说明：

• COUNT(*) OVER (PARTITION BY a.acc_no)会把相同acc_no的行划分为一个分组，计算该分组的总行数，并将数值填充到分组内的每一行，正好满足total_num列的需求。
• 保留了你原查询的排序逻辑，确保结果顺序符合预期。

方法二：子查询关联（兼容不支持窗口函数的老版本SQL） #

如果你的数据库版本不支持窗口函数（如MySQL 5.7及更早版本），可以用子查询关联的方式实现：

SELECT 
    base.acc_no,
    base.id,
    cnt.cou AS total_num,
    base.status
FROM (
    -- 获取基础关联数据
    SELECT a.acc_no, a.id, b.status
    FROM table1 a
    INNER JOIN table2 b
        ON a.sid = b.sid
    WHERE b.type = 'SMT'
        AND b.status = 'Active'
) AS base
-- 关联acc_no的统计结果
INNER JOIN (
    SELECT cnt.acc_no, COUNT(1) AS cou 
    FROM (
        SELECT a.acc_no
        FROM table1 a
        INNER JOIN table2 b
            ON a.sid = b.sid
        WHERE b.type = 'SMT'
            AND b.status = 'Active'
    ) AS cnt
    GROUP BY cnt.acc_no
) AS cnt ON base.acc_no = cnt.acc_no
ORDER BY base.acc_no, base.id;

说明：

• 先通过base子查询取出所有符合条件的行数据，再通过cnt子查询统计每个acc_no的总行数，最后通过acc_no字段关联两个子查询，将计数填充到对应行。

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