分组和加入联合表。有问题 [英] Grouping and Joining a Unioned Table. Having Problems

问题描述

有一天我问了一个问题，并收到了一些非常有用的信息，这些信息帮助我得到了这一点。但是，我现在在使用UNION / GROUP BY从表中获取数据时遇到了问题。

以下代码是我尝试过的。但我似乎无法找到一种让玩家分组的方式。

它是这样显示的。

  Jono  -  3  -  1 
 Jono  -  1  -  1 
  

当我需要像这样展示

  Jono  -  4  -  1 
  

  SELECT [玩家]，[玩过]，[游戏元] FROM 
（SELECT 
 p1.displayname AS [玩家]，
 COUNT（p1.displayname）AS [玩过]，
 SUM （IIF（m.player2 = m.winner，1,0））AS [Games Won] 
 FROM（（tblMatch m）
 INNER JOIN tblPlayers p1 ON m.player2 = p1.id）
 WHERE m.season = 3 
 GROUP BY p1.displayname 
 UNION ALL 
 SELECT 
 p2.displayname AS [Player2]，
 COUNT（p2.displayname） AS [玩过]，
 SUM（IIF（m.player1 = m.winner，1,0））AS [游戏币] 
 FROM（（tblMatch m）
 INNER JOIN tblPlayers p2 ON m.player1 = p2.id）
 WHERE m.season = 3 
 GROUP BY p2.displayname）
  

我的数据库layo ut是：

  tblPlayers（ID，名字，姓氏，显示名称，让分，当前）
 tblSeason（ID，季节）
 tblMatch（ID，MatchDate，Season，Player1，Player2，Player1Score，Player2Score，Winner）
  

我真正想要做的是显示一个列表，其中玩家名称可以来自玩家1或玩家2列，并计算该玩家玩的游戏数量，并从这些游戏中计数他们赢得游戏的次数。

谢谢

解决方案

在你的外部查询中：

  SELECT [InnerQueryAlias]。[Player]，Sum（[InnerQueryAlias]。[Played]）AS SumOfPlayed ，Sum（[InnerQueryAlias]。[Games Won]）AS [SumOfGames Won] 
 FROM（SELECT 
 p1.displayname AS [Player]，
 COUNT（p1.displayname）AS [已玩] ，
 SUM（IIF（m.player2 = m.winner，1,0））AS [Games Won] 
 FROM（（tblMatch m）
 INNER JOIN tblPlayers p1 ON m.player2 = p1.id）
 WHERE m.season = 3 
 GROUP BY p1.displayname 
 UNION ALL 
 SELECT 
 p2.displayname AS [Player2]，
 COUNT（p2.displayname）AS [玩过]，
 SUM（IIF（m.player1 = m.winner，1,0））AS [Games Won] 
 FROM（（tblMatch m）
 INNER JOIN tblPlayers p2 ON m.player1 = p2.id）
 WHERE m.season = 3 
 GROUP BY p2.displayname）AS [InnerQueryAlias] 
 GROUP BY [InnerQueryAlias]。[Player ]。 
  

The other day I asked a question and received some very helpful information which assisted me to get this far. However I am now having trouble with taking data from tables using UNION/GROUP BY.

The following code is what I have tried. But I cannot seem to find a way to make it group by Player.

It is showing like this.

Jono - 3 - 1
Jono - 1 - 1

When I need to show like this

Jono - 4 - 1 

I am just beginning so if there is a better way to perform this code, please tell me :)

SELECT [Player],[Played],[Games Won] FROM
(SELECT
p1.displayname AS [Player],
COUNT(p1.displayname) AS [Played],
SUM(IIF(m.player2=m.winner, 1, 0)) AS [Games Won]
FROM ((tblMatch m)
INNER JOIN tblPlayers p1 ON m.player2=p1.id)
WHERE m.season = 3
GROUP BY p1.displayname
UNION ALL
SELECT
p2.displayname AS [Player2],
COUNT(p2.displayname) AS [Played],
SUM(IIF(m.player1=m.winner, 1, 0)) AS [Games Won]
FROM ((tblMatch m)
INNER JOIN tblPlayers p2 ON m.player1=p2.id)
WHERE m.season = 3
GROUP BY p2.displayname)

My Database layout is:

tblPlayers (ID,FirstName,LastName,DisplayName,Handicap,Current)  
tblSeason  (ID,Season)  
tblMatch   (ID,MatchDate,Season,Player1,Player2,Player1Score,Player2Score,Winner)  

And what I would really like to do is show a list where the players name can come from either Player1 or Player2 column and Count the number of games played by this player and From these games Count the number of times they won the game.

Thanks

解决方案

Wouldn't you just put another group by in your outer query:

SELECT [InnerQueryAlias].[Player], Sum([InnerQueryAlias].[Played]) AS SumOfPlayed, Sum([InnerQueryAlias].[Games Won]) AS [SumOfGames Won]
FROM (SELECT
p1.displayname AS [Player],
COUNT(p1.displayname) AS [Played],
SUM(IIF(m.player2=m.winner, 1, 0)) AS [Games Won]
FROM ((tblMatch m)
INNER JOIN tblPlayers p1 ON m.player2=p1.id)
WHERE m.season = 3
GROUP BY p1.displayname
UNION ALL
SELECT
p2.displayname AS [Player2],
COUNT(p2.displayname) AS [Played],
SUM(IIF(m.player1=m.winner, 1, 0)) AS [Games Won]
FROM ((tblMatch m)
INNER JOIN tblPlayers p2 ON m.player1=p2.id)
WHERE m.season = 3
GROUP BY p2.displayname)  AS [InnerQueryAlias]
GROUP BY [InnerQueryAlias].[Player];

这篇关于分组和加入联合表。有问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！