mysql两表的差集_mysql两表汇总后再得到两表的交集和差集，请问怎么实现？

恩,我原先的逻辑,是 先 关联,后合计, 现在看来有些问题. 需要修改一下:

修改为 先合计, 后 关联.

SELECT

IFNULL(A.`C1`, B.`C1`) AS `区域`,

IFNULL(A.`C2`, B.`C2`) AS `省份`,

IFNULL(A.`C3`, B.`C3`) AS `城市`,

IFNULL(A.`C4`, B.`C4`) AS `编码`,

IFNULL(A.`C5`, 0) AS `表A销量`,

IFNULL(B.`C5`, 0) AS `表B销量`

FROM

(SELECT C1, C2, C3, C4, SUM(C5) AS C5 FROM A GROUP BY C1, C2, C3, C4) A

LEFT JOIN (SELECT C1, C2, C3, C4, SUM(C5) AS C5 FROM B GROUP BY C1, C2, C3, C4) B

ON (

A.`C1` = B.`C1` AND

A.`C2` = B.`C2` AND

A.`C3` = B.`C3` AND

A.`C4` = B.`C4`

)

UNION

SELECT

IFNULL(A.`C1`, B.`C1`) AS `区域`,

IFNULL(A.`C2`, B.`C2`) AS `省份`,

IFNULL(A.`C3`, B.`C3`) AS `城市`,

IFNULL(A.`C4`, B.`C4`) AS `编码`,

IFNULL(A.`C5`, 0) AS `表A销量`,

IFNULL(B.`C5`, 0) AS `表B销量`

FROM

(SELECT C1, C2, C3, C4, SUM(C5) AS C5 FROM A GROUP BY C1, C2, C3, C4) A

RIGHT JOIN (SELECT C1, C2, C3, C4, SUM(C5) AS C5 FROM B GROUP BY C1, C2, C3, C4) B

ON (

A.`C1` = B.`C1` AND

A.`C2` = B.`C2` AND

A.`C3` = B.`C3` AND

A.`C4` = B.`C4`

);

+------+------+------+----------+---------+---------+

| 区域 | 省份 | 城市 | 编码 | 表A销量 | 表B销量 |

+------+------+------+----------+---------+---------+

| 华北 | 辽宁 | 沈阳 | 20020202 | 2 | 5 |

| 华北 | 北京 | 北京 | 7193 | 7 | 2 |

| 华北 | 北京 | 北京 | 8358 | 5 | 0 |

| 华北 | 北京 | 北京 | 9773 | 8 | 0 |

| 华东 | 山东 | 青岛 | 18020133 | 8 | 6 |

| 华东 | 甘肃 | 兰州 | 7364 | 0 | 3 |

| 华东 | 湖北 | 武汉 | 7512 | 0 | 9 |

| 华东 | 江苏 | 常州 | 7582 | 0 | 5 |

| 华东 | 江苏 | 淮安 | 7670 | 0 | 7 |

+------+------+------+----------+---------+---------+

9 rows in set (0.00 sec)