MySql 多表分组统计

1. 多表关联统计，（rollup在每个分组下都会有统计汇总）

SELECT quxian, SUM(cnt) cnt,type  from 
(
select xiangongsi quxian,count(taiqubianma) cnt, '1' type from ypgt_wudiyayonghutaiqushu GROUP BY xiangongsi
 UNION ALL
select quxian,count(taiquhao) cnt, '1' type from ypgt_tongtaiquyhsybtgds GROUP BY quxian  
 UNION ALL
select quxian,count(taiqubianma) cnt, '1' type from ypgt_tytqxyyh GROUP BY quxian  
 UNION ALL
select quxian,count(xiangbiaohao) cnt, '2' type from ypgt_tbxbtgds GROUP BY quxian    
 UNION ALL
select quxian,count(xbh) cnt , '2' type from ypgt_tbxxyhcghu GROUP BY quxian  
 UNION ALL
select quxian,count(xiangbiaohao) cnt , '2' type from ypgt_tbxbtdydjyh GROUP BY quxian
) t GROUP BY quxian,type 
with rollup

2. 多表分组统计，行转列

SELECT
    quxian,
    SUM(
        CASE `type`
        WHEN '1' THEN
            cnt
        ELSE
            0
        END
    ) AS 'd1',
    SUM(
        CASE `type`
        WHEN '2' THEN
            cnt
        ELSE
            0
        END
    ) AS 'd2',
    SUM(
        CASE `type`
        WHEN '3' THEN
            cnt
        ELSE
            0
        END
    ) AS 'd3',
    SUM(cnt) hj
FROM
    (
        SELECT
            quxian,
            1 type,
            count(cnt1) cnt
        FROM
            (
                SELECT
                    quxian,
                    count(gongdiansuo) cnt1
                FROM
                    ypgt_tbxbtgds
                WHERE
                    date_format(create_date, '%Y-%m') ='2020-05'
                GROUP BY
                    xiangbiaohao,
                    quxian
                HAVING
                    count(gongdiansuo) > 1
            ) t
        GROUP BY
            quxian
        UNION ALL
            SELECT
                quxian,
                2 type,
                count(DISTINCT xbh) cnt
            FROM
                ypgt_tbxxyhcghu
            WHERE
                date_format(create_date, '%Y-%m') ='2020-05'
            GROUP BY
                quxian
            UNION ALL
                SELECT
                    quxian,
                    3 type,
                    count(DISTINCT xiangbiaohao) cnt
                FROM
                    ypgt_tbxbtdydjyh
                WHERE
                    date_format(create_date, '%Y-%m') ='2020-05'
                GROUP BY
                    quxian
    ) t
GROUP BY
    quxian;

3. 多表分组，全连接（其实可通过第二种方式实现）

SELECT
    IFNULL(aqx, bqx) city,
    IFNULL(50b, 0) 50b,
    IFNULL(5b, 0) 5b
FROM
    (
        (
            SELECT
                a.qx aqx,
                b.qx bqx,
                50b,
                5b
            FROM
                (
                    SELECT
                        qx,
                        count(1) 50b
                    FROM
                        ypgt_tqrl50
                    WHERE
                        date_format(create_date, '%Y-%m') ='2020-05'
                    GROUP BY
                        qx
                ) a
            LEFT JOIN (
                SELECT
                    qx,
                    count(1) 5b
                FROM
                    ypgt_hjxy
                WHERE
                    date_format(create_date, '%Y-%m') ='2020-05'
                GROUP BY
                    qx
            ) b ON a.qx = b.qx
        )
        UNION
            (
                SELECT
                    a.qx aqx,
                    b.qx bqx,
                    50b,
                    5b
                FROM
                    (
                        SELECT
                            qx,
                            count(1) 50b
                        FROM
                            ypgt_tqrl50
                        WHERE
                            date_format(create_date, '%Y-%m') ='2020-05'
                        GROUP BY
                            qx
                    ) a
                RIGHT JOIN (
                    SELECT
                        qx,
                        count(1) 5b
                    FROM
                        ypgt_hjxy
                    WHERE
                        date_format(create_date, '%Y-%m') ='2020-05'
                    GROUP BY
                        qx
                ) b ON a.qx = b.qx
            )
    ) t
ORDER BY
    city;

MySql 多表分组统计

推荐阅读