Sql查询获取计数不同的匹配id

问题描述

我想选择比赛次数/玩家ID和总和（目标）



匹配ID玩家ID目标

1 119 1

1 120 1

1 142 1

1 119 0

1 120 1

1 137 1

2 119 1

2 120 1

3 119 1



我尝试过：

I want to select Number of Match played/Player Id and sum(Goal)

Match Id Player Id Goal
1 119 1
1 120 1
1 142 1
1 119 0
1 120 1
1 137 1
2 119 1
2 120 1
3 119 1

What I have tried:

SELECT tmp.[Player Id], Count(tmp.[Match Id]) AS MatchPlayed
FROM (SELECT DISTINCT
          [Match Id],[Player Id]
      FROM [Match Details]
    )  AS tmp
GROUP BY tmp.[Player Id];

推荐答案

SELECT tmp.[Player Id], SUM(tmp.[MatchPlayed) AS MatchPlayed, SUM(t2.Goal) AS PointsReached
FROM (SELECT DISTINCT [Player Id], COUNT([Match Id]) As MatchPlayed
      FROM [Match Details]
      GROUP BY [Player Id]
    ) AS tmp INNER JOIN [Match Details] AS t2 ON tmp.[Player Id] = t2.[Player Id]
GROUP BY tmp.[Player Id];

详情请见： []

[]

SELECT [Player Id], Count([Match Id]) AS MatchPlayed, Sum([Goal]) AS GoalCount
FROM [Match Details] GROUP BY [Player Id]

这篇关于Sql查询获取计数不同的匹配id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！