建站
咨詢
售后
建站咨詢
新聞中心
mysql兩表匯總后再得到兩表的交集和差集，請(qǐng)問(wèn)怎么實(shí)現(xiàn)？
CREATE TABLE A (
成都創(chuàng)新互聯(lián)公司主要從事成都網(wǎng)站設(shè)計(jì)、成都做網(wǎng)站、外貿(mào)網(wǎng)站建設(shè)、網(wǎng)頁(yè)設(shè)計(jì)、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)鋼城,十年網(wǎng)站建設(shè)經(jīng)驗(yàn),價(jià)格優(yōu)惠、服務(wù)專業(yè),歡迎來(lái)電咨詢建站服務(wù):18980820575
`C1` VARCHAR(10),
`C2` VARCHAR(10),
`C3` VARCHAR(10),
`C4` VARCHAR(10),
`C5` INT
);
INSERT INTO A
SELECT '華北', '遼寧', '沈陽(yáng)', '20020202', 2 UNION ALL
SELECT '華東', '山東', '青島', '18020133', 3 UNION ALL
SELECT '華北', '北京', '北京', '7193', 7 UNION ALL
SELECT '華北', '北京', '北京', '8358', 5 UNION ALL
SELECT '華北', '北京', '北京', '9773', 8 UNION ALL
SELECT '華東', '山東', '青島', '18020133', 5 ;
CREATE TABLE B (
`C1` VARCHAR(10),
`C2` VARCHAR(10),
`C3` VARCHAR(10),
`C4` VARCHAR(10),
`C5` INT
);
INSERT INTO B
SELECT '華北', '遼寧', '沈陽(yáng)', '20020202', 5 UNION ALL
SELECT '華東', '山東', '青島', '18020133', 6 UNION ALL
SELECT '華北', '北京', '北京', '7193', 2 UNION ALL
SELECT '華東', '甘肅', '蘭州', '7364', 3 UNION ALL
SELECT '華東', '湖北', '武漢', '7512', 7 UNION ALL
SELECT '華東', '湖北', '武漢', '7512', 2 UNION ALL
SELECT '華東', '江蘇', '常州', '7582', 5 UNION ALL
SELECT '華東', '江蘇', '淮安', '7670', 7;
SELECT
IFNULL(A.`C1`, B.`C1`) AS `區(qū)域`,
IFNULL(A.`C2`, B.`C2`) AS `省份`,
IFNULL(A.`C3`, B.`C3`) AS `城市`,
IFNULL(A.`C4`, B.`C4`) AS `編碼`,
SUM(A.`C5`) AS `表A銷量`,
SUM(B.`C5`) AS `表B銷量`
FROM
A LEFT JOIN B
ON (
A.`C1` = B.`C1` AND
A.`C2` = B.`C2` AND
A.`C3` = B.`C3` AND
A.`C4` = B.`C4`
)
GROUP BY
IFNULL(A.`C1`, B.`C1`),
IFNULL(A.`C2`, B.`C2`),
IFNULL(A.`C3`, B.`C3`),
IFNULL(A.`C4`, B.`C4`)
UNION
SELECT
IFNULL(A.`C1`, B.`C1`) AS `區(qū)域`,
IFNULL(A.`C2`, B.`C2`) AS `省份`,
IFNULL(A.`C3`, B.`C3`) AS `城市`,
IFNULL(A.`C4`, B.`C4`) AS `編碼`,
SUM(A.`C5`) AS `表A銷量`,
SUM(B.`C5`) AS `表B銷量`
FROM
A RIGHT JOIN B
ON (
A.`C1` = B.`C1` AND
A.`C2` = B.`C2` AND
A.`C3` = B.`C3` AND
A.`C4` = B.`C4`
)
GROUP BY
IFNULL(A.`C1`, B.`C1`),
IFNULL(A.`C2`, B.`C2`),
IFNULL(A.`C3`, B.`C3`),
IFNULL(A.`C4`, B.`C4`);
+------+------+------+----------+---------+---------+
| 區(qū)域 | 省份 | 城市 | 編碼 | 表A銷量 | 表B銷量 |
+------+------+------+----------+---------+---------+
| 華北 | 遼寧 | 沈陽(yáng) | 20020202 | 2 | 5 |
| 華北 | 北京 | 北京 | 7193 | 7 | 2 |
| 華北 | 北京 | 北京 | 8358 | 5 | NULL |
| 華北 | 北京 | 北京 | 9773 | 8 | NULL |
| 華東 | 山東 | 青島 | 18020133 | 8 | 12 |
| 華東 | 甘肅 | 蘭州 | 7364 | NULL | 3 |
| 華東 | 湖北 | 武漢 | 7512 | NULL | 9 |
| 華東 | 江蘇 | 常州 | 7582 | NULL | 5 |
| 華東 | 江蘇 | 淮安 | 7670 | NULL | 7 |
+------+------+------+----------+---------+---------+
9 rows in set (0.00 sec)
mysql有沒(méi)有求差集的內(nèi)部函數(shù)
只是查詢，不做刪除吧： SELECT * FROM 表1 WHERE name NOT IN ( SELECT name FROM 表2 )
mysql里面求兩個(gè)表的差集
只是查詢，不做刪除吧：
SELECT * FROM 表1 WHERE name NOT IN (
SELECT name FROM 表2
)
mysql數(shù)據(jù)庫(kù)如何做這樣的差集查詢？
select?URL?from?TABLEA?
where???url?not?in?(select?URL?from?TABLEB?where?APP_NAME=TABLEA.APP_NAME)
本文題目：mysql中怎么查詢差集 mysql 集合差
URL鏈接：http://ef60e0e.cn/article/hpsjsc.html
