MySQL分组后统计各品牌出现次数的实现问题

现有一个按salesman和brand分组的MySQL查询，需要在结果中新增一列，显示每个brand在分组后结果中的总出现次数（比如brand 'aaa'在分组结果里共出现3次，那么每条对应'aaa'的记录都要显示这个3）。

尝试直接添加count(*)，仅能得到每个销售对应品牌的出现次数，不符合需求；尝试子查询时，触发Error Code: 1054（未知列INMASTER.BRAND）错误，使用的是MySQL 5.1.60版本。

原始查询代码：

select
    ARM.SALESMAN AS 'SalesmanNumber',
    INM.BRAND AS 'Brand'
from
    ARMASTER ARM
    LEFT JOIN ARTRAN ART ON ARM.NUMBER = ART.CUST_NO
    LEFT JOIN INTRAN INTR ON ART.REF = INTR.REF
    LEFT JOIN ARSALECD SALESMAN ON ARM.SALESMAN = SALESMAN.CODE
    LEFT JOIN INMASTER INM ON INTR.STOCK_CODE = INM.CODE
where
    ARM.AREA = 01
    AND ARM.CUSTTYPE <> '99'
    AND ARM.SALESMAN NOT IN (24,48,49,50,51,52,71,72,74,90)
    AND (
        (YEAR(INTR.DATE) = YEAR(@mth) AND MONTH(INTR.DATE) = MONTH(@mth))
        OR (YEAR(DATE_ADD(@mth, INTERVAL -1 MONTH)) AND MONTH(INTR.DATE) = MONTH(DATE_ADD(@mth, INTERVAL -1 MONTH)))
        OR (YEAR(DATE_ADD(@mth, INTERVAL -2 MONTH)) AND MONTH(INTR.DATE) = MONTH(DATE_ADD(@mth, INTERVAL -2 MONTH)))
        )
group by Brand , SalesmanNumber;

尝试的子查询代码片段：

ARM.SALESMAN AS 'SalesmanNumber',
INM.BRAND AS 'Brand',
(
SELECT
    count(*)
FROM
    INMASTER AS s
WHERE
    s.BRAND=INMASTER.BRAND
) AS brand_count

方法1：子查询预统计分组后品牌出现次数 #

先通过子查询得到原始分组结果，再单独统计每个品牌在分组结果中的总次数，最后关联回主查询：

SELECT
    t.SalesmanNumber,
    t.Brand,
    bc.brand_total_count
FROM
    (
        -- 原始分组查询逻辑
        SELECT
            ARM.SALESMAN AS 'SalesmanNumber',
            INM.BRAND AS 'Brand'
        FROM
            ARMASTER ARM
            LEFT JOIN ARTRAN ART ON ARM.NUMBER = ART.CUST_NO
            LEFT JOIN INTRAN INTR ON ART.REF = INTR.REF
            LEFT JOIN ARSALECD SALESMAN ON ARM.SALESMAN = SALESMAN.CODE
            LEFT JOIN INMASTER INM ON INTR.STOCK_CODE = INM.CODE
        WHERE
            ARM.AREA = 01
            AND ARM.CUSTTYPE <> '99'
            AND ARM.SALESMAN NOT IN (24,48,49,50,51,52,71,72,74,90)
            AND (
                (YEAR(INTR.DATE) = YEAR(@mth) AND MONTH(INTR.DATE) = MONTH(@mth))
                OR (YEAR(DATE_ADD(@mth, INTERVAL -1 MONTH)) = YEAR(INTR.DATE) AND MONTH(INTR.DATE) = MONTH(DATE_ADD(@mth, INTERVAL -1 MONTH)))
                OR (YEAR(DATE_ADD(@mth, INTERVAL -2 MONTH)) = YEAR(INTR.DATE) AND MONTH(INTR.DATE) = MONTH(DATE_ADD(@mth, INTERVAL -2 MONTH)))
                )
        GROUP BY Brand , SalesmanNumber
    ) t
    LEFT JOIN (
        -- 统计分组后每个品牌的总出现次数
        SELECT
            Brand,
            COUNT(*) AS brand_total_count
        FROM
            (
                SELECT
                    INM.BRAND AS 'Brand'
                FROM
                    ARMASTER ARM
                    LEFT JOIN ARTRAN ART ON ARM.NUMBER = ART.CUST_NO
                    LEFT JOIN INTRAN INTR ON ART.REF = INTR.REF
                    LEFT JOIN ARSALECD SALESMAN ON ARM.SALESMAN = SALESMAN.CODE
                    LEFT JOIN INMASTER INM ON INTR.STOCK_CODE = INM.CODE
                WHERE
                    ARM.AREA = 01
                    AND ARM.CUSTTYPE <> '99'
                    AND ARM.SALESMAN NOT IN (24,48,49,50,51,52,71,72,74,90)
                    AND (
                        (YEAR(INTR.DATE) = YEAR(@mth) AND MONTH(INTR.DATE) = MONTH(@mth))
                        OR (YEAR(DATE_ADD(@mth, INTERVAL -1 MONTH)) = YEAR(INTR.DATE) AND MONTH(INTR.DATE) = MONTH(DATE_ADD(@mth, INTERVAL -1 MONTH)))
                        OR (YEAR(DATE_ADD(@mth, INTERVAL -2 MONTH)) = YEAR(INTR.DATE) AND MONTH(INTR.DATE) = MONTH(DATE_ADD(@mth, INTERVAL -2 MONTH)))
                        )
                GROUP BY Brand , ARM.SALESMAN
            ) sub
        GROUP BY Brand
    ) bc ON t.Brand = bc.Brand;

方法2：用户变量实现（兼容MySQL 5.1） #

通过初始化用户变量，先按品牌排序后批量赋值，减少重复子查询：

SELECT
    SalesmanNumber,
    Brand,
    brand_total_count
FROM
    (
        SELECT
            t.SalesmanNumber,
            t.Brand,
            @count := IF(@current_brand = t.Brand, @count, bc.total) AS brand_total_count,
            @current_brand := t.Brand
        FROM
            (
                -- 原始分组查询并按品牌排序
                SELECT
                    ARM.SALESMAN AS 'SalesmanNumber',
                    INM.BRAND AS 'Brand'
                FROM
                    ARMASTER ARM
                    LEFT JOIN ARTRAN ART ON ARM.NUMBER = ART.CUST_NO
                    LEFT JOIN INTRAN INTR ON ART.REF = INTR.REF
                    LEFT JOIN ARSALECD SALESMAN ON ARM.SALESMAN = SALESMAN.CODE
                    LEFT JOIN INMASTER INM ON INTR.STOCK_CODE = INM.CODE
                WHERE
                    ARM.AREA = 01
                    AND ARM.CUSTTYPE <> '99'
                    AND ARM.SALESMAN NOT IN (24,48,49,50,51,52,71,72,74,90)
                    AND (
                        (YEAR(INTR.DATE) = YEAR(@mth) AND MONTH(INTR.DATE) = MONTH(@mth))
                        OR (YEAR(DATE_ADD(@mth, INTERVAL -1 MONTH)) = YEAR(INTR.DATE) AND MONTH(INTR.DATE) = MONTH(DATE_ADD(@mth, INTERVAL -1 MONTH)))
                        OR (YEAR(DATE_ADD(@mth, INTERVAL -2 MONTH)) = YEAR(INTR.DATE) AND MONTH(INTR.DATE) = MONTH(DATE_ADD(@mth, INTERVAL -2 MONTH)))
                        )
                GROUP BY Brand , SalesmanNumber
                ORDER BY Brand
            ) t
            CROSS JOIN (SELECT @current_brand := '', @count := 0) vars
            CROSS JOIN (
                -- 预统计每个品牌的总出现次数
                SELECT
                    Brand,
                    COUNT(*) AS total
                FROM
                    (
                        SELECT
                            INM.BRAND AS 'Brand'
                        FROM
                            ARMASTER ARM
                            LEFT JOIN ARTRAN ART ON ARM.NUMBER = ART.CUST_NO
                            LEFT JOIN INTRAN INTR ON ART.REF = INTR.REF
                            LEFT JOIN ARSALECD SALESMAN ON ARM.SALESMAN = SALESMAN.CODE
                            LEFT JOIN INMASTER INM ON INTR.STOCK_CODE = INM.CODE
                        WHERE
                            ARM.AREA = 01
                            AND ARM.CUSTTYPE <> '99'
                            AND ARM.SALESMAN NOT IN (24,48,49,50,51,52,71,72,74,90)
                            AND (
                                (YEAR(INTR.DATE) = YEAR(@mth) AND MONTH(INTR.DATE) = MONTH(@mth))
                                OR (YEAR(DATE_ADD(@mth, INTERVAL -1 MONTH)) = YEAR(INTR.DATE) AND MONTH(INTR.DATE) = MONTH(DATE_ADD(@mth, INTERVAL -1 MONTH)))
                                OR (YEAR(DATE_ADD(@mth, INTERVAL -2 MONTH)) = YEAR(INTR.DATE) AND MONTH(INTR.DATE) = MONTH(DATE_ADD(@mth, INTERVAL -2 MONTH)))
                                )
                        GROUP BY Brand , ARM.SALESMAN
                    ) sub
                GROUP BY Brand
            ) bc
        WHERE t.Brand = bc.Brand
        ORDER BY t.Brand, t.SalesmanNumber
    ) final;

你之前的子查询报错有两个问题：

1. 子查询中引用了未别名的INMASTER.BRAND，但外层查询里INMASTER的别名是INM，应该改为s.BRAND=INM.BRAND；
2. 即便修正别名，该子查询统计的是整个INMASTER表中该品牌的总条数，并非分组后结果中的出现次数，逻辑不符合需求。

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