java - SQL COUNT 显示的不仅仅是计数？

Question

我有一个包含大量板球碗的数据库。架构是:

Player(PlayerID, Initials, Surname)
 - Stores all the players
Bowl(BowlID, Striker, Non-Striker, Bowler, Runs)
 - Stores info for every ball
Team(TeamID, name)
 - Stores all the teams
SquadPlayer(TeamID, PlayerID, MatchID)
 - Stores a record of who was playing at each match and for which team

我正在尝试找出英格兰队中每位球员得分的六分数。 (我使用的是openoffice风格的HSQL)

SELECT "Player"."Initials", "Player"."Surname", COUNT ("Bowl"."Striker") AS "No. Sixes"
FROM "Bowl", "Player", "Player" AS "Bowler"
WHERE "Bowl"."Striker" = "Player"."PlayerID"
AND "Bowl"."Bowler" = "Bowler"."PlayerID"
AND "Bowl"."Striker" IN (
    SELECT DISTINCT "Player"."PlayerID" 
    FROM "Player", "Team", "SquadPlayer"
    WHERE "Player"."PlayerID" = "SquadPlayer"."PlayerID" 
    AND "Team"."TeamID" = "SquadPlayer"."TeamID" 
    AND "Team"."Name" = 'England' )
AND "Bowl"."Runs" = '6'
GROUP BY "Bowl"."Striker"

这是我尝试运行的 SQL，但它不起作用。如果我省略 SELECT 部分中的前两列(Player.Initials 和 Player.Surname)，它可以正常工作，但它不是很有用，因为我无法计算出谁得分全部为 6。

我通过java运行它，我得到了异常:

java.sql.SQLException: Not in aggregate function or group by clause

那么为什么这不起作用呢？如何在其旁边显示名称？

Answer 1

当 select 子句中有计数(或任何聚合函数)时，select 子句中的所有未聚合列也必须出现在 group-by 子句中。

让你的分组条款看起来像这样:

GROUP BY "Bowl"."Striker", "Player"."Initials", "Player"."Surname"

它应该可以工作..