我有一个数据库，其中包含运动队比赛统计数据和记录的记录，而我目前在弄清楚如何检索该赛季整个团队的总积分并将其合并到查询中以返回其总胜利数和损失。
我目前没有显示反对的查询是：
SELECT league_team_data.`Team Name` AS TeamName, league_team_data.TeamID, games.Result, count(*) AS Total, sum(case when games.`Result` = 'WIN' then 1 else 0 end) GamesWon, sum(case when games.`Result` = 'LOSS' then 1 else 0 end) GamesLost, sum(case when games.`Result` = 'WIN' then 2 when games.`Result` = 'TIE' then 1 else 0 end) Points, SUM(Score) PointsFor
FROM league_team_data
LEFT JOIN games
ON games.TeamID = league_team_data.TeamID
INNER JOIN teams_in_divisions
ON teams_in_divisions.DivisionID_FK = 2 AND games.TeamID = teams_in_divisions.TeamID_FK
GROUP BY TeamID
ORDER BY PointsFor DESC, Points DESC
此处硬编码的'2'一直保持硬编码，因为它可以正常工作。
返回其总得分的查询是：
SELECT SUM(Score) FROM `games` WHERE TeamID <> 1 AND
GameID IN (SELECT GameID FROM games WHERE TeamID = 1)
需要更改此处的硬编码“ 1”，才能与上述查询中的League_team_data.TeamID一起使用。
因此，我需要将第二个查询合并到第一个查询中，以便为“ Points Against”添加一列，这是第二个查询返回的内容。
“游戏”表中包含以下数据（以及更多数据，但这与该问题有关）：
GameID | TeamID | Score |
1 3 20
1 5 28
每行是游戏中涉及到的一个团队的游戏统计信息，因此“得分”列是团队得分。因此，为了获得与团队的对分，我需要对所有他们打过的具有GameID的行的“得分”列进行求和，但不计算其行的得分，而应计算其对手的行。第二个查询完成此操作。
抱歉，如果这是一个非常基本的问题（我觉得我缺少明显的东西！），但是任何帮助将不胜感激！
根据要求，以下是创建表语句：
表League_team_data：
CREATE TABLE `league_team_data` (
`TeamID` mediumint(10) NOT NULL AUTO_INCREMENT,
`Team Name` varchar(50) DEFAULT NULL,
`Team Name Short` varchar(50) DEFAULT NULL,
`Team Name Initials` varchar(10) DEFAULT NULL,
`Team Background Image` varchar(100) DEFAULT NULL,
`Team Logo` varchar(100) DEFAULT NULL,
PRIMARY KEY (`TeamID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
桌上游戏：
CREATE TABLE `games` (
`Game Date` date NOT NULL,
`GameTime` time NOT NULL,
`GameID` mediumint(10) NOT NULL,
`TeamID` mediumint(10) NOT NULL,
`Team` varchar(50) NOT NULL,
`Score` int(5) NOT NULL,
`Result` varchar(5) NOT NULL,
`Home Team` varchar(50) NOT NULL,
`HomeTeamID` mediumint(10) NOT NULL,
`Away Team` varchar(50) NOT NULL,
`AwayTeamID` mediumint(10) NOT NULL,
`Game Status` varchar(10) NOT NULL,
`SeasonID` int(10) NOT NULL,
PRIMARY KEY (`GameID`,`TeamID`),
CONSTRAINT `FK_gameid` FOREIGN KEY (`GameID`) REFERENCES `league_games` (`GameID`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
表格team_in_divisions：
CREATE TABLE `teams_in_divisions` (
`DivisionID_FK` mediumint(10) NOT NULL,
`TeamID_FK` mediumint(10) NOT NULL,
UNIQUE KEY `DivisionID_TeamID` (`DivisionID_FK`,`TeamID_FK`),
KEY `TeamID_FK` (`TeamID_FK`),
CONSTRAINT `teams_in_divisions_ibfk_2` FOREIGN KEY (`TeamID_FK`) REFERENCES `league_team_data` (`TeamID`),
CONSTRAINT `teams_in_divisions_ibfk_1` FOREIGN KEY (`DivisionID_FK`) REFERENCES `league_divisions` (`DivisionID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
最佳答案
事实证明，虽然我不确定这种查询的优化程度，但是有一种简单的方法可以完成我想做的事情。
我只需要将子查询直接添加到原始SELECT语句中即可。这是最终的代码：
SELECT league_team_data.`Team Name` AS TeamName,
league_team_data.TeamID, games.Result, count(*) AS Total,
sum(case when games.`Result` = 'WIN' then 1 else 0 end) GamesWon,
sum(case when games.`Result` = 'LOSS' then 1 else 0 end) GamesLost,
sum(case when games.`Result` = 'WIN' then 2 when games.`Result` = 'TIE' then 1 else 0 end) Points,
SUM(Score) PointsFor,
(SELECT SUM(Score) AS PointsAgainst FROM `games` WHERE TeamID <> league_team_data.TeamID AND GameID IN (SELECT GameID FROM games WHERE TeamID = league_team_data.TeamID)) AS PointsAgainst
FROM league_team_data
LEFT JOIN games ON games.TeamID = league_team_data.TeamID
INNER JOIN teams_in_divisions ON teams_in_divisions.DivisionID_FK = 1 AND games.TeamID = teams_in_divisions.TeamID_FK
GROUP BY TeamID ORDER BY Points DESC
我添加的部分是“ SELECT SUM（Score）AS PointsAgainst ...”行。
再次，这可能是非常低效的，但是确实有效！
关于mysql - 将带有子查询的联接合并到现有查询中，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/26327618/