mysql - 2 MySQL 查询中的 LEFT JOIN

Question

我试图在一个表中列出所有比赛，用户是否参加了每场比赛，以及每场比赛的参赛总数。

这是表格:

CREATE TABLE `competition` (
`competitionID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` char(255) NOT NULL DEFAULT '',
`description` varchar(750) NOT NULL DEFAULT '',
`startDate` date DEFAULT NULL,
`endDate` date DEFAULT NULL,
`isLive` tinyint(1) NOT NULL,
PRIMARY KEY (`competitionID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `competition` (`competitionID`, `name`, `description`, 
`startDate`, `endDate`, `isLive`)
VALUES
(1,'Win a car','Win a really cool car!','2018-04-01 09:30:27','2019-04-01 09:30:27',1),
(2,'Another competition','Win something even better!','2018-04-01 09:30:27','2019-04-01 09:30:27',1);

CREATE TABLE `competition_entrant` (
`competitionEntrantID` int(11) NOT NULL AUTO_INCREMENT,
`userID` int(11) NOT NULL,
`competitionID` int(11) NOT NULL,
PRIMARY KEY (`competitionEntrantID`),
UNIQUE KEY `userID` (`userID`,`competitionID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `competition_entrant` (`competitionEntrantID`, `userID`, 
`competitionID`)
VALUES
(1,1,1),
(2,1,2),
(3,2,1);

所以在这个例子中，id 为 1 的用户已经参加了两个比赛，id 为 2 的用户已经参加了 id 1 的比赛。

这是我的查询。

SELECT
`c`.`name`,
COUNT(`ce1`.`userID`) AS 'hasEnteredCompetition',
COUNT(`ce2`.`userID`) AS 'totalEntries'
FROM competition c
LEFT JOIN `competition_entrant` `ce1` ON `c`.`competitionID` = 
`ce1`.`competitionID`
AND `ce1`.`userID` = 2
LEFT JOIN `competition_entrant` `ce2` ON `c`.`competitionID` = 
`ce2`.`competitionID`
GROUP BY (c.competitionID);

问题是 hasEnteredCompetition 显示的是条目总数，而不仅仅是输入的用户的 1 个，即该用户的计数。

谁能告诉我我做错了什么？

Answer 1

您两次加入 competition_entrant 表，因此用户“2”条目被拉取两次。你可以这样看:

SELECT C.COMPETITIONID,C.NAME,CE1.USERID,CE1.COMPETITIONID
FROM COMPETITION C
LEFT JOIN COMPETITION_ENTRANT CE1 ON C.COMPETITIONID = CE1.COMPETITIONID AND CE1.USERID = 2
LEFT JOIN COMPETITION_ENTRANT CE2 ON C.COMPETITIONID = CE2.COMPETITIONID


1   Win a car           2       1
2   Another competition null    null        
1   Win a car           2       1

您可以像这样向您的查询添加一个不同的计数:

select C.NAME,C.COMPETITIONID,
COUNT(DISTINCT CE1.USERID) as "hasEnteredCompetition",
COUNT(CE2.USERID) as "totalEntries"
from COMPETITION C
left join COMPETITION_ENTRANT CE1 on C.COMPETITIONID = CE1.COMPETITIONIDand CE1.USERID = 2
left join COMPETITION_ENTRANT CE2 on C.COMPETITIONID = CE2.COMPETITIONID
group by (C.NAME,C.COMPETITIONID)