sql - 查找每个团队本垒打最高的人(DERBY/SQL)

Question

首先，这是家庭作业，但我绝不是在寻找答案。我在一个数据库类中，我们得到了一个包含美国棒球联盟统计数据(团队，每个人的统计数据等)的数据库，每个表格的模式定义在问题下方。我需要编写的查询是这样的:

在同一支球队中，哪对击球手本垒打最多？您的查询应提供配对中每个击球手的名字和姓氏，以及每次击中的本垒打次数。

因此，基本上，每个团队只获得两个最高的本垒打，看看哪个团队拥有最高的本垒打，但是我很难编写一个查询来生成每个团队得分最高的人(就本垒打而言)。到目前为止，我有这样的事情。

select 
    nameFirst, nameLast, name, HR
from 
    Players, Teams, Batting
where 
    HR >= ALL(select HR from Batting) 
    and Players.playerID = Batting.playerID;

它显示正确的球队名称(我知道这是因为它显示30个元组，这就是数据库中的球队数量)，但是本垒打的数量以及玩家的名字和姓氏都相同。 (在此数据库中，酿酒师是菲尔王子(Prince Fielders)，因为他在数据库中拥有最多的本垒打。)有关实现方法的提示是，它可以显示正确的人的名字和姓氏，不胜感激!

CREATE TABLE Players
(
    playerID VARCHAR(10), --A unique code asssigned to each player. The playerID
    --links the data in this file with records in the other files.
    nameFirst VARCHAR(50), --First name
    nameLast VARCHAR(50), --Last name
    bats CHAR(1), --Player's batting hand (left, right, or both)
    throws CHAR(1), --Player's throwing hand (left or right)
    PRIMARY KEY (playerID)
);

CREATE TABLE Teams
(
    teamID CHAR(3), --Three-letter team code
    lgID CHAR(2), --Two-letter league code
    divID CHAR(1), --One letter code for the division the team player in
    Rank SMALLINT, --Position in final standings in that division
    G SMALLINT, --Games played
    W SMALLINT, --Games won
    L SMALLINT, --Games lost
    DivWin CHAR(1), --Division winner (Y or N)
    WCWin CHAR(1), --Wild card winner (Y or N)
    LgWin CHAR(1), --League champion (Y or N)
    WSWin CHAR(1), --World series winner
    name VARCHAR(50), --Team's full name
    park VARCHAR(255), --Name of team's home ballpark
    PRIMARY KEY (teamID)
);

CREATE TABLE Batting
(
    playerID VARCHAR(10), --Player ID code
    yearID SMALLINT, --Will always be 2011 in data for this assignment
    stint SMALLINT, --Used to identify a particular continuous period that
    --a player played for one team during the season. For example, a player
    --who played during May for the Brewers, was then sent down to the
    --minors and came back to play again for the Brewers in August would
    --have two stints -- numbered 1 and 2
    teamID CHAR(3), --Three-letter team ID
    lgID CHAR(2), --Two letter league ID -- NL or AL
    G SMALLINT, --Number of games appeared in during this stint
    G_batting SMALLINT, --Number of games appeared in as a batter during this stint
    AB SMALLINT, --Number of at bats
    R SMALLINT, --Number of runs
    H SMALLINT, --Number of hits
    doubles SMALLINT, --Number of doubles
    triples SMALLINT, --Number of triples
    HR SMALLINT, --Number of home runs
    RBI SMALLINT, --Number of runs batted in
    SB SMALLINT, --Number of stolen bases
    CS SMALLINT, --Number of times caught trying to steal a base
    BB SMALLINT, --Number of base on balls (walks)
    SO SMALLINT, --Number of time player struck out
    IBB SMALLINT, --Number of intentional walks received
    HBP SMALLINT, --Number of time hit by pitch
    SF SMALLINT, --Number of sacrifice flied
    GIDP SMALLINT, --Number of times grounded into double play
    PRIMARY KEY (playerID, stint)
);

Answer 1

我假设HR在Batter表中，即使它似乎不在表定义中。但是，您需要做的就是将击球员表加入自身。您可以在同一查询中多次查询同一张表，只需为每个表实例指定不同的别名即可。

通过将击球员表与其自身相连，您将创建所谓的“笛卡尔积”，该乘积基本上是该表中每两个击球员的组合。从那里，您可以计算HR的总和，然后相应地按该值排序。

如果您愿意，我会为您提供查询，但是我知道您说这是家庭作业，因此，如果您想根据我的解释进行尝试，可以提出任何问题，并可以从那里提供帮助:)

祝好运!