MySQL查询中使用双LEFT JOIN：获取赛事列表及参与相关信息

Got it, let's work through this problem step by step. First, I notice you only shared the competition table structure—we'll need an additional table to track which users have joined which competitions. This is a standard many-to-many relationship setup, so let's assume we have a competition_participant table with this structure (adjust if your actual table differs):

CREATE TABLE `competition_participant` (
  `competitionID` int(11) unsigned NOT NULL,
  `userID` int(11) unsigned NOT NULL,
  `joinTimestamp` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`competitionID`, `userID`),
  FOREIGN KEY (`competitionID`) REFERENCES `competition`(`competitionID`),
  FOREIGN KEY (`userID`) REFERENCES `users`(`userID`) -- Assuming you have a core users table
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

With that in place, here's the SQL query that will fetch all competitions, show if a specific user (replace 123 with your target user ID) has joined each event, and count the total number of participants per competition:

SELECT
  c.competitionID,
  c.name,
  c.description,
  c.startDate,
  c.endDate,
  c.isLive,
  -- Check if the target user has participated
  IF(cp.userID IS NOT NULL, 1, 0) AS hasParticipated,
  -- Count total participants for the competition
  COUNT(cp_total.userID) AS totalParticipants
FROM competition c
-- Left join to check target user's participation
LEFT JOIN competition_participant cp
  ON c.competitionID = cp.competitionID AND cp.userID = 123
-- Left join to count all participants
LEFT JOIN competition_participant cp_total
  ON c.competitionID = cp_total.competitionID
GROUP BY c.competitionID, c.name, c.description, c.startDate, c.endDate, c.isLive, hasParticipated;

Quick breakdown of how this works: #

• First LEFT JOIN: This links the competition table to the participant table only for your target user. If the user hasn't joined a competition, cp.userID will be NULL, so our IF statement returns 0 (not participated) or 1 (participated).
• Second LEFT JOIN: This separate join lets us count every user who's joined each competition—even if the target user hasn't joined that event.
• GROUP BY: We group by all competition fields plus the hasParticipated flag to ensure each competition appears exactly once with its correct metrics.

If your participation table has a different name or extra columns, just tweak the join conditions and column names to match your actual schema!

内容的提问来源于stack exchange，提问作者Shaun