Sql查询获取计数不同的匹配id [英] Sql query to get count distinct match 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];

推荐答案

初步说明：在MS SQL Server上你可以使用： SELECT COUNT（DISTINCT< FieldName>）FROM ... ，但在MS Access数据库上，例如语句将导致错误。 ;（



所以，你可以选择以下方式之一：

1）子查询

2）加入table



这应该按预期工作：

Preliminary note: on MS SQL Server you can use: SELECT COUNT(DISTINCT <FieldName>) FROM ..., but on MS Access database such as statement will cause an error. ;(

So, you can choose one of the way:
1) subquery
2) joined table

This should work as expected:

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];

详情请见：访问子查询技术 [ ^ ]

Access 2007中的查询提示和技巧 [ ^ ]

未经测试但类似

Untested but something like

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

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