如何从具有不同结构的多个表中总和？

Question

嗨，我正在尝试通过使用SQL查询来总结“喜欢”＆amp; 量

“不喜欢”的

奖励的“数量”和减去 作为我的..

这是我的第一张桌子“文章”

用户	bob	名喜欢
3	-1	，

这是我的第二个表“奖项”奖励“

用户	名
bob	3

在这种情况下，它应该输出5 ，但我不能让它上班，

 SELECT SUM(rep) AS totalRep FROM 
  (
    SELECT `likes` FROM `articles` UNION ALL
    SELECT `dislikes` FROM `articles` UNION ALL
    SELECT `amount` FROM `awards` UNION ALL
  ) GROUP BY `username`

我也尝试过这样的..

SELECT SUM(rep) AS totalRep FROM 
  (
    SELECT SUM(likes) FROM `articles` UNION ALL
    SELECT SUM(dislikes) FROM `articles` UNION ALL
    SELECT SUM(amount) FROM `awards` UNION ALL
  ) GROUP BY `username`

和这样的..

SELECT rep SUM(totrep) AS totrep FROM 
(
  SELECT likes,dislikes FROM `articles`
  UNION ALL
  SELECT amount FROM `awards`
) GROUP BY `username`

和..

SELECT SUM(likes,dislikes,amount) AS totrep
  FROM (
        SELECT likes,dislikes FROM `articles`
        UNION ALL
        SELECT amount FROM `awards`
       )  GROUP BY `username`

Answer 1

对于文章表，您需要总结喜欢+不喜欢的内容，然后使用所有联合。

尝试：

select username,sum(amount) as totalRep
FROM ( 
       select username,
              (sum(likes)+sum(dislikes)) as amount
       from articles
       group by username
    union all
       select username,
              amount
     from awards
     ) as t1
group by  username;