A表
userId pagerId amount28 1 200
14 4 10
28 4 500
14 14 20
B表:
userId pagerId amount14 1 100
14 14 -12两表中userId与pagerId都相等就将他们的amount字段数量相加或减
当B表中有与A表userId与pagerId不相等的记录就将B表中不相等记录于A表合并我要的查询结果如下:
userId pagerId amount28 1 200
14 4 10
28 4 500
14 14 8(就是20-12的结果)
14 1 100
userId pagerId amount28 1 200
14 4 10
28 4 500
14 14 20
B表:
userId pagerId amount14 1 100
14 14 -12两表中userId与pagerId都相等就将他们的amount字段数量相加或减
当B表中有与A表userId与pagerId不相等的记录就将B表中不相等记录于A表合并我要的查询结果如下:
userId pagerId amount28 1 200
14 4 10
28 4 500
14 14 8(就是20-12的结果)
14 1 100
FROM a, b
WHERE a.userId = b.userId
AND a.pageId = b.pageId
UNION ALL
SELECT a.userId, a.pageId, a.amount
FROM a
WHERE NOT EXISTS (SELECT 1 FROM b WHERE a.userId = b.userId AND a.pageId = b.pageId)
UNION ALL
SELECT b.userId, b.pageId, b.amount
FROM b
WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.userId = b.userId AND a.pageId = b.pageId)
FROM a LEFT OUTER JOIN b
ON a.userId = b.userId
AND a.pageId = b.pageId
UNION
SELECT b.userId, b.pageId, IFNULL(a.amount, 0) + b.amount
FROM a RIGHT OUTER JOIN b
ON a.userId = b.userId
AND a.pageId = b.pageId
from
(select * from a
union all
select * from b) t
group by userid,pagerid;
from A表 left join B表 using(userId,pagerId)
什么意思
第一个SELECT,选择a, b两者有交集的
第二个选择a有,b没有
第三个选择b有,a没有第二个SQL,左连+右连
using(userId,pagerId)是不是相当于a.userId=b.userId and b.pagerId=a.pagerId