Mysql通过UNION完成总计

来源：互联网发布：淘宝客佣金到账编辑：程序博客网时间：2024/06/12 01:03
  SELECT        C1,        C3 ,        C4  ,        C5  ,        C6  ,        C7  ,        C8  ,        C9  ,        C10  ,        C11  ,        C12  ,        C13  ,        C14  ,        C15  ,        C16  ,        C17  ,        C18  ,        C19      FROM        ( SELECT            b.sec_dept_short_name AS c1,            b.sec_dept_code AS c2 ,            SUM(IF(a.bj_id,            1,            0)) AS c3,            SUM(CASE                WHEN bj_type=1 THEN 1                ELSE 0            END) AS c4,            SUM(CASE                WHEN bj_type=2 THEN 1                ELSE 0            END) AS c5,            SUM(CASE                WHEN bj_type=3 THEN 1                ELSE 0            END) AS c6,            SUM(CASE                WHEN is_passed=0 THEN 1                ELSE 0            END) AS c7,            SUM(CASE                WHEN is_passed=1 THEN 1                ELSE 0            END) AS c8,            SUM(CASE                WHEN is_changed=0 THEN 1                ELSE 0            END) AS c9,            SUM(CASE                WHEN is_changed=1 THEN 1                ELSE 0            END) AS c10,            SUM(CASE                WHEN has_fw=0 THEN 1                ELSE 0            END) AS c11,            SUM(CASE                WHEN has_fw=1 THEN 1                ELSE 0            END) AS c12,            SUM(CASE                WHEN has_gate=0 THEN 1                ELSE 0            END) AS c13,            SUM(CASE                WHEN has_gate=1 THEN 1                ELSE 0            END) AS c14,            SUM(CASE                WHEN has_gap=0 THEN 1                ELSE 0            END) AS c15,            SUM(CASE                WHEN has_gap=1 THEN 1                ELSE 0            END) AS c16,            SUM(CASE                WHEN a.bj_id IS NOT NULL                AND other_info IS NOT NULL                  AND other_info <> '' THEN 1                ELSE 0            END) AS c17,            SUM(CASE                WHEN a.bj_id IS NOT NULL                AND ( other_info IS NULL                OR other_info ='') THEN 1                ELSE 0            END) AS c18,            SUM(IF(AA.BJ_ID,            1,            0))  AS c19          FROM            bj_base_info a        RIGHT OUTER JOIN            tbl_basic_security_dept b                ON a.sf=b.sec_dept_short_name          LEFT JOIN            (                SELECT                    BJ_BASE_INFO.BJ_ID                   FROM                    BJ_BASE_INFO                     WHERE                    EXISTS  (                        SELECT                            1                        FROM                            BJ_YW_INFO YW                        WHERE                            YW.BJ_ID = BJ_BASE_INFO.BJ_ID                            AND NETWORK_TYPE = 2                              AND data_exchange IN (                                2,3                            )                    )                  ) AA                    ON (                        AA.BJ_ID = A.BJ_ID                    )              LEFT JOIN                (                    SELECT                        BJ_BASE_INFO.BJ_ID                       FROM                        BJ_BASE_INFO                        WHERE                        EXISTS  (                            SELECT                                1                            FROM                                BJ_YW_INFO YW                            WHERE                                YW.BJ_ID = BJ_BASE_INFO.BJ_ID                                AND NETWORK_TYPE = 2                                  AND  data_exchange NOT IN (                                    2,3                                )                        )                      ) BB                        ON (                            BB.BJ_ID = A.BJ_ID                        )                  WHERE                    b.upper_dept=2                    AND 1=1                      AND b.sec_dept_short_name IN (                        '公安部直属单位','北京市','天津市','河北省','山西省','内蒙古自治区','辽宁省','吉林省','黑龙江省','上海市','江苏省','浙江省','安徽省','福建省','江西省','山东省','河南省','湖北省','湖南省','广东省','广西壮族自治区','海南省','重庆市','四川省','贵州省','云南省','西藏自治区','陕西省','甘肃省','青海省','宁夏回族自治区','新疆维吾尔自治区','新疆生产建设兵团'                    )                  GROUP BY                    b.sec_dept_short_name ,                    b.sec_dept_code                    UNION                SELECT                    '总计' AS c1,                    '999999999999' AS c2,                    SUM(IF(a.bj_id,                    1,                    0)) AS c3,                    SUM(CASE                        WHEN bj_type=1 THEN 1                        ELSE 0                    END) AS c4,                    SUM(CASE                        WHEN bj_type=2 THEN 1                        ELSE 0                    END) AS c5,                    SUM(CASE                        WHEN bj_type=3 THEN 1                        ELSE 0                    END) AS c6,                    SUM(CASE                        WHEN is_passed=0 THEN 1                        ELSE 0                    END) AS c7,                    SUM(CASE                        WHEN is_passed=1 THEN 1                        ELSE 0                    END) AS c8,                    SUM(CASE                        WHEN is_changed=0 THEN 1                        ELSE 0                    END) AS c9,                    SUM(CASE                        WHEN is_changed=1 THEN 1                        ELSE 0                    END) AS c10,                    SUM(CASE                        WHEN has_fw=0 THEN 1                        ELSE 0                    END) AS c11,                    SUM(CASE                        WHEN has_fw=1 THEN 1                        ELSE 0                    END) AS c12,                    SUM(CASE                        WHEN has_gate=0 THEN 1                        ELSE 0                    END) AS c13,                    SUM(CASE                        WHEN has_gate=1 THEN 1                        ELSE 0                    END) AS c14,                    SUM(CASE                        WHEN has_gap=0 THEN 1                        ELSE 0                    END) AS c15,                    SUM(CASE                        WHEN has_gap=1 THEN 1                        ELSE 0                    END) AS c16,                    SUM(CASE                        WHEN a.bj_id IS NOT NULL                        AND other_info IS NOT NULL                        AND other_info <> '' THEN 1                        ELSE 0                    END) AS c17,                    SUM(CASE                        WHEN a.bj_id IS NOT NULL                        AND (other_info IS NULL                        OR other_info ='') THEN 1                        ELSE 0                    END) AS c18,                    SUM(IF(AA.BJ_ID,                    1,                    0))  AS c19                  FROM                    bj_base_info a                RIGHT OUTER JOIN                    tbl_basic_security_dept b                        ON a.sf=b.sec_dept_short_name                  LEFT JOIN                    (                        SELECT                            BJ_BASE_INFO.BJ_ID                           FROM                            BJ_BASE_INFO                             WHERE                            EXISTS  (                                SELECT                                    1                                FROM                                    BJ_YW_INFO YW                                WHERE                                    YW.BJ_ID = BJ_BASE_INFO.BJ_ID                                    AND NETWORK_TYPE = 2                                      AND  data_exchange IN (                                        2,3                                    )                            )                          ) AA                            ON (                                AA.BJ_ID = A.BJ_ID                            )                      LEFT JOIN                        (                            SELECT                                BJ_BASE_INFO.BJ_ID                               FROM                                BJ_BASE_INFO                                WHERE                                EXISTS  (                                    SELECT                                        1                                    FROM                                        BJ_YW_INFO YW                                    WHERE                                        YW.BJ_ID = BJ_BASE_INFO.BJ_ID                                        AND NETWORK_TYPE = 2                                          AND data_exchange NOT IN (                                            2,3                                        )                                )                              ) BB                                ON (                                    BB.BJ_ID = A.BJ_ID                                )                          WHERE                            b.upper_dept=2                            AND 1=1                              AND b.sec_dept_short_name IN (                                '公安部直属单位','北京市','天津市','河北省','山西省','内蒙古自治区','辽宁省','吉林省','黑龙江省','上海市','江苏省','浙江省','安徽省','福建省','江西省','山东省','河南省','湖北省','湖南省','广东省','广西壮族自治区','海南省','重庆市','四川省','贵州省','云南省','西藏自治区','陕西省','甘肃省','青海省','宁夏回族自治区','新疆维吾尔自治区','新疆生产建设兵团'                            )                        ORDER BY                            2                    ) A
0 0