我有两张桌子:Score table和Teams table.它们都包含来自其他表的一些外键，但在我的例子中，我不需要来自其他表的列.
我想拿到SELECT query分的team id and points分.这些要点是由一个COUNT of MAX score of a team per round点组成的.
Score table:
| id | round_id | team_id | score |
|---|---|---|---|
| 1085e36b-7621-4634-93e2-c5404108ed23 | 424d4186-4432-4b8d-ab9d-00ee71945cf6 | 752f9ebc-7016-42f2-be90-768711fc3d46 | 18 |
| 47ff3765-6357-47bc-abb8-6b5adf436546 | 424d4186-4432-4b8d-ab9d-00ee71945cf6 | 90dd8de4-8ddc-46cd-8b67-d93edbede174 | 7 |
| b26b1a5b-8606-4c08-b838-30fdbcf98697 | 3fbb49a0-7a8a-4db5-b854-84a9ce7ac2b3 | 90dd8de4-8ddc-46cd-8b67-d93edbede174 | 20 |
| c1add78a-94a3-48cf-b89d-4e4500ab738d | 3fbb49a0-7a8a-4db5-b854-84a9ce7ac2b3 | 752f9ebc-7016-42f2-be90-768711fc3d46 | 21 |
Teams table:
| id | name | tournament_id |
|---|---|---|
| 2b56a499-9fc4-40c2-a7c4-43762c2b27e7 | 2@1.com & 4@1.com | 9aaa6f86-5555-49b8-99a6-900a90dc3c7c |
| 752f9ebc-7016-42f2-be90-768711fc3d46 | 5@1.com & 3@1.com | 9aaa6f86-5555-49b8-99a6-900a90dc3c7c |
| 7c9aa32a-ff99-4169-9512-27bc0aac0093 | 7@1.com & 6@1.com | 9aaa6f86-5555-49b8-99a6-900a90dc3c7c |
| 90dd8de4-8ddc-46cd-8b67-d93edbede174 | 8@1.com & 1@1.com | 9aaa6f86-5555-49b8-99a6-900a90dc3c7c |
你可以看到team have a tournament_id foreign key from a Tournaments table和Score table have a round_id foreign key from a Rounds table，但在这一步中我不需要这些细节.
正如你在Score table中看到的，对于round，有two records, one for each team that plays in that round个，我想要know for any team how many times did it won a round个(这就是为什么我猜我需要一个MAX的计数).
我试过的SELECT query美元:
SELECT team_id, count(max(score)) as points FROM Score
WHERE team_id IN (SELECT DISTINCT t.id
FROM Teams t
WHERE t.tournament_id = '9aaa6f86-5555-49b8-99a6-900a90dc3c7c')
GROUP BY round_id
但这query美元却把Error Code: 1111. Invalid use of group function美元抛给了我.
预期结果:
| team_id | points |
|---|---|
| 752f9ebc-7016-42f2-be90-768711fc3d46 | 2 |
| 90dd8de4-8ddc-46cd-8b67-d93edbede174 | 0 |
我该如何达到预期的结果？
谢谢你抽出时间！如果有什么不清楚或应该有更多的细节，让我知道！