MySQL:如何构造给定的查询

Question

我根本不是MySQL大师，如果有人花点时间帮我，我会很感激的。我有三张表如下所示：

TEAM(teamID, teamName, userID)
YOUTH_TEAM(youthTeamID, youthTeamName, teamID)
YOUTH_PLAYER(youthPlayerID, youthPlayerFirstName, youthPlayerLastName, youthPlayerAge, youthPlayerDays, youthPlayerRating, youthPlayerPosition, youthTeamID)

这就是我现在的疑问：

SELECT team.teamName, youth_team.youthTeamName, youth_player.*
FROM youth_player
    INNER JOIN youth_team ON youth_player.youthTeamID = youth_team.youthTeamID
    INNER JOIN team ON youth_team.teamID = team.teamID
WHERE youth_player.youthPlayerAge < 18
AND youth_player.youthPlayerDays < 21
AND youth_player.youthPlayerRating >= 5.5

我想在这个查询中添加一个更彻底的检查，如下所示：

• 如果球员有16年的时间，并且他的位置是得分手，那么球员应该至少有7个等级才能被归还。
• 如果球员有15年的时间，并且他的位置是球员，那么该球员至少应该有5.5分才能被退回。
• 等等

我如何在我的查询中实现这些需求(如果可能的话)，并且该查询会是一个糟糕的解决方案吗？如果我使用PHP代码进行选择(假设我使用PHP)，而不是在查询中这样做，会更好吗？

Answer 1

这里有一个附加的“标准/过滤器”表的可能解决方案：

-- SAMPLE TEAMS: Yankees, Knicks:
INSERT INTO `team` VALUES (1,'Yankees',2),(2,'Knicks',1);

-- SAMPLE YOUTH TEAMS: Yankees Juniors, Knicks Juniors
INSERT INTO `youth_team` VALUES (1,'Knicks Juniors',1),(2,'Yankees Juniors',2);

-- SAMPLE PLAYERS
INSERT INTO `youth_player` VALUES       
  (1,'Carmelo','Anthony',16,20,7.5,'scorer',1),
  (2,'Amar\'e','Stoudemire',17,45,5.5,'playmaker',1),
  (3,'Iman','Shumpert',15,15,6.1,'playmaker',1),
  (4,'Alex','Rodriguez',18,60,3.5,'playmaker',2),
  (5,'Hiroki','Kuroda',16,17,8.7,'scorer',2),
  (6,'Ichiro','Suzuki',19,73,8.3,'playmaker',2);

-- CRITERIA TABLE
CREATE TABLE `criterias` (
  `id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `position` varchar(45) DEFAULT NULL,
  `min_rating` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- SAMPLE CRITERIAS
-- AGE=16, POSITION=SCORER, MIN_RATING=7
-- AGE=15, POSITION=PLAYMAKER, MIN_RATING=5.5
INSERT INTO `criterias` VALUES (1,16,'scorer',7), (2,15,'playmaker',5.5);

现在，您的查询可能如下所示：

SELECT team.teamName, youth_team.youthTeamName, youth_player.*
FROM youth_player
    CROSS JOIN criterias
    INNER JOIN youth_team ON youth_player.youthTeamID = youth_team.youthTeamID
    INNER JOIN team ON youth_team.teamID = team.teamID
WHERE 
(
    youth_player.youthPlayerAge < 18
    AND youth_player.youthPlayerDays < 21
    AND youth_player.youthPlayerRating >= 5.5
) 
AND
(
    youth_player.youthPlayerAge = criterias.age
    AND youth_player.youthPlayerPosition = criterias.position
    AND youth_player.youthPlayerRating >= criterias.min_rating
)

这产生(缩短的结果)：

teamName  youthTeamName     youthPlayerName   Age   Days  Rating  Position   
=============================================================================
Yankees   "Knicks Juniors"  Carmelo Anthony   16    20    7.5     scorer        
Yankees   "Knicks Juniors"  Iman Shumpert     15    15    6.1     playmaker   
Knicks    "Yankees Juniors" Hiroki Kuroda     16    17    8.7     scorer       

Answer 2

在查询中这样做很好.只要不搞砸就行。您可以在查询中执行很多内容，但是维护起来可能会变得很困难。因此，如果它太长，并且您希望其他人来查看它，您应该将其拆分，或者在php-script中找到解决方案。

至于您的要求，请添加以下内容：

AND 
(
    (YOUTH_PLAYER.youthPlayerAge >= 16 AND YOUTH_PLAYER.youthPlayerPosition = 'scorer' AND YOUTH_PLAYER.youthPlayerRating >= 7)
    OR (YOUTH_PLAYER.youthPlayerAge >= 15 AND YOUTH_PLAYER.youthPlayerPosition = 'playmaker' AND YOUTH_PLAYER.youthPlayerRating >= 5.5)
)