返回错误，就像使用组

Question

似乎一旦在查询中使用了Group，我就开始在where子句中返回错误。

SELect Rtrim(riders.firstName)||' '||rtrim(riders.lastname) AS Rider_Name, teams.teamname, Rtrim(agents.firstName)||' '||rtrim(agents.lastname) AS Agent_Name
FROM Riders, teams, agents, participation, races
Where teams.teamID = riders.teamID
and agents.agentID = riders.agentID
and participation.riderID = riders.riderid
and races.raceid = participation.raceid
and races.RaceDate Between '01-Apr-2008' and '30-Apr-2008'

返回我需要的结果，但我需要消除任何重复，所以我已经做了

Group BY Rtrim(riders.firstName)||' '||rtrim(riders.lastname)

和

Group BY participation.riderID

和

Group By rider.riderID

这是我最好的猜测。这是完整的代码。

SQL> SELect Rtrim(riders.firstName)||' '||rtrim(riders.lastname) AS Rider_Name,
teams.teamname, Rtrim(agents.firstName)||' '||rtrim(agents.lastname) AS Agent_Na
me
  2  FROM Riders, teams, agents, participation, races
  3  Where teams.teamID = riders.teamID
  4  and agents.agentID = riders.agentID
  5  and participation.riderID = riders.riderid
  6  and races.raceid = participation.raceid
  7  and races.RaceDate Between '01-Apr-2008' and '30-Apr-2008'
  8  Group BY participation.riderid
  9  ;
SELect Rtrim(riders.firstName)||' '||rtrim(riders.lastname) AS Rider_Name, teams
.teamname, Rtrim(agents.firstName)||' '||rtrim(agents.lastname) AS Agent_Name
             *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

Answer 1

如果您想要消除完全重复的行，可以只使用select distinct

SELect DISTINCT Rtrim(riders.firstName)||' '||rtrim(riders.lastname) AS Rider_Name,
       teams.teamname, Rtrim(agents.firstName)||' '||rtrim(agents.lastname) AS Agent_Name

那就不要使用group by。

Answer 2

我建议rider.riderID使用Group

只需将rider.riderID添加到字段列表中即可。

FWIW组比区分组快