我想加入两张桌子。
TableA
+-------------------+--------+
| wordA(primarykey) | countA |
+-------------------+--------+
| abc | 25 |
| abcd | 29 |
| abcde | 45 |
+-------------------+--------+
TableB
+-------------------+--------+
| wordB(primarykey) | countB |
+-------------------+--------+
| ab | 10 |
| abc | 40 |
| abcde | 90 |
| abcdef | 55 |
+-------------------+--------+
期望的输出：
TableC
+--------+--------+--------+
| word | countA | countB |
+--------+--------+--------+
| ab | 0 | 10 |
| abc | 25 | 40 |
| abcd | 29 | 0 |
| abcde | 45 | 90 |
| abcdef | 0 | 55 |
+--------+--------+--------+
我想在 TableC 中插入所需输出的值。请提供一些代码。我尝试了这个，但我遇到的问题是我无法合并 wordA 和 wordB。
INSERT INTO TableC
SELECT
t.word,
SUM(COALESCE(a.countA, 0)) AS CountA,
SUM(COALESCE(b.countB, 0)) AS countB
FROM
(
SELECT wordA AS word FROM tableA
UNION
SELECT wordB FROM tableB
) AS t
LEFT JOIN tableA AS a on t.word = a.wordA
LEFT JOIN tableB AS b on t.word = b.wordb
GROUP BY t.word
SQL 小提琴演示 http://www.sqlfiddle.com/#!2/ed220/1
这会给你：
| WORD | COUNTA | COUNTB |
|--------|--------|--------|
| ab | 0 | 10 |
| abc | 25 | 40 |
| abcd | 29 | 0 |
| abcde | 45 | 90 |
| abcdef | 0 | 55 |
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
