我想创建一个存储过程,它接受球队名称和年份,并返回该特定年份该特定球队的球员人数。我正在使用MYSQL,下面列出的是我正在使用的表格:
Person (personID, name, phone, email, year)
Player (personID, dateOfBirth, school)
playerTeam (personID, teamID)
Team (teamID, teamName, ageGroup)
到目前为止,我已经尝试加入表格并仅在 teamName 和 Person.year = 输入参数的情况下查找 personID,但无济于事。有什么想法吗?
提交此问题后,通过反复试验,我能够使用以下语句使其工作:
DELIMITER //
CREATE PROCEDURE teamSize (IN team VARCHAR (30), IN YEAR INT)
BEGIN
SELECT
COUNT(Person.personID) NumOfPlayers
FROM
Person AS p,
Player,
playerTeam,
Team
WHERE Person.personID = Player.personID
AND Player.personID = playerTeam.personID
AND Team.teamID = playerTeam.teamID
AND Team.teamName = team
AND Person.year = YEAR ;
END //
DELIMITER ;
CALL teamSize('Pirates', 1991);
无论如何可以使用连接等来简化这一点,因为目前它有点混乱。
我不确定你是否想要这样:
DELIMITER //
CREATE PROCEDURE GetPlayerCount (
IN oTeamName VARCHAR (50),
IN oYear INT
) AS
BEGIN
SELECT
COUNT(a.PersonID) NoOfPlayers
FROM
Person a
INNER JOIN PlayerTeam b
ON a.PersonID = b.PersonID
INNER JOIN Team c
ON b.TeamID = c.TeamID
WHERE b.TeamName = oTeamName
AND a.Year = oYear ;
END //
DELIMITER ;
但我认为这是你想要的。
用法:
CALL GetPlayerCount('TeamDog', 2012);