mysql-将多个SUM查询合并为一个结果

标签：multiple-tables sum sql mysql sql-server

所以…我们有3个与比赛有关的表格,其中DB跟踪他们为每个比赛获得的积分.竞赛1、2和3.每当用户获得成就时,就会为该用户创建一个新行,并附加得分.因此,为了计算用户获得的所有积分,我使用选择总和

SELECT userID, SUM(amount1) as "Contest 1 Points"
FROM [Company].[dbo].[Contest1]
WHERE userid not in (0,1)
GROUP BY userId
ORDER BY userid

因为我还有另外两个比赛,所以我也要查询每个比赛…

SELECT userId, SUM(amount2)/.65 AS "Category 2 Points"
FROM [Company].[dbo].[Contest2]
WHERE dateGiven >=201301 AND dateGiven <= 201305
GROUP BY userId
ORDER BY userid



SELECT userid, SUM(amount3) AS "Category 3 Points"
FROM [Company].[dbo].[Contest3]
where userid not in (1,2)
GROUP BY userid
ORDER BY userid

我基本上需要将每个用户从每个竞赛中获得的所有积分加到1个基本上显示结果的列中
USERID,共TOTALS(竞赛1竞赛2竞赛3)

或至少像

USER,共1个竞赛,共2个竞赛,共3个竞赛

到目前为止,我这样做的方法是将每个结果复制/粘贴到excel中,然后我使用VLOOKUP将它们相互匹配,这有点麻烦,并且肯定有一种方法可以在SQL中完成.我对SQL来说还很陌生,我曾尝试加入并使用usig ON来匹配userid,但是我的语法以及我了解到查询都可以插入其中的方式出了点问题.

解决方法:

您需要UNION结果：

SELECT userID, SUM(Points) AS total
FROM
 ( 
   SELECT userID, SUM(amount1) AS "Points"
   FROM [Company].[dbo].[Contest1]
   WHERE userid NOT IN (0,1)
   GROUP BY userId

   UNION ALL       

   SELECT userId, SUM(amount2)/.65 AS "Category 2 Points"
   FROM [Company].[dbo].[Contest2]
   WHERE dateGiven >=201301 AND dateGiven <= 201305
   GROUP BY userId

   UNION ALL       

   SELECT userid, SUM(amount3) AS "Category 3 Points"
   FROM [Company].[dbo].[Contest3]
   WHERE userid NOT IN (1,2)
   GROUP BY userid
 ) AS dt
GROUP BY userID
ORDER BY 2 DESC;

编辑：
要获得三个单独的列,您只需使用三个SUM而不是一个：

SELECT userID, SUM("Category 1 Points"), SUM("Category 2 Points"), SUM("Category 3 Points") 
FROM
 ( 
   SELECT userID, SUM(amount1) AS "Category 1 Points"
   FROM [Company].[dbo].[Contest1]
   WHERE userid NOT IN (0,1)
   GROUP BY userId

   UNION ALL       

   SELECT userId, SUM(amount2)/.65 AS "Category 2 Points"
   FROM [Company].[dbo].[Contest2]
   WHERE dateGiven >=201301 AND dateGiven <= 201305
   GROUP BY userId

   UNION ALL       

   SELECT userid, SUM(amount3) AS "Category 3 Points"
   FROM [Company].[dbo].[Contest3]
   WHERE userid NOT IN (1,2)
   GROUP BY userid
 ) AS dt
GROUP BY userID
ORDER BY 2 DESC;

当然,每个userDI /类别只有一行,因此MIN或MAX将返回相同的结果.
如果想要0,则将为不存在的数据返回NULL,而应使用COALESCE(“ Category x Points”,0).

您也可以加入结果集,但是除非保证每个用户都参加了每个比赛,否则您需要使用COALESCE进行FULL OUTER JOIN：

SELECT userID, "Category 1 Points", "Category 2 Points", "Category 3 Points"
FROM
 ( 
   SELECT userID, SUM(amount1) AS "Category 1 Points"
   FROM [Company].[dbo].[Contest1]
   WHERE userid NOT IN (0,1)
   GROUP BY userId
 ) AS t1
FULL JOIN
ON t1.userID = t2.userID
 (
   SELECT userId, SUM(amount2)/.65 AS "Category 2 Points"
   FROM [Company].[dbo].[Contest2]
   WHERE dateGiven >=201301 AND dateGiven <= 201305
   GROUP BY userId
 ) AS t2
FULL JOIN
 (
   SELECT userid, SUM(amount3) AS "Category 3 Points"
   FROM [Company].[dbo].[Contest3]
   WHERE userid NOT IN (1,2)
   GROUP BY userid
 ) AS t3
ON COALESCE(t1.userID, t2.userID) = t3.userID
ORDER BY 2 DESC;

 

标签：multiple-tables,sum,sql,mysql,sql-server
来源： https://codeday.me/bug/20191122/2062214.html