直接看案例代码和截图:
mysql数据库中有两张表,a表和b表;
a表
b表
有这么道题目:将a表和b表合并,并且让a表中和b表中id相同的行的num相加;
方式1:
#方式1
SELECT a.id, a.num + b.num AS '相加' FROM a,b WHERE a.id = b.id
UNION ALL
SELECT b.id, b.num FROM b WHERE b.id NOT IN(SELECT a.id FROM a WHERE a.id = b.id);
SELECT a.id, a.num + b.num AS '相加' FROM a,b WHERE a.id = b.id
UNION
SELECT b.id, b.num FROM b WHERE b.id NOT IN(SELECT a.id FROM a WHERE a.id = b.id);
方式2:
#方式2
SELECT temp.id, SUM(temp.num) FROM
(SELECT * FROM a UNION ALL SELECT * FROM b) AS temp GROUP BY temp.id;