Question

为了扩展我上一期的内容，我的数据库中有两个表，我想提取某些信息。下表：

   (player): player_id (primary), playerName
    (match): match_id (primary), playerID1, playerID2, playerID3, scorer etc..

您向我提供了以下代码以获取玩家的姓名：

SELECT p.Name 
FROM `match` m
INNER JOIN `player` p 
    ON p.player_id IN (m.playerID1, m.playerID2, m.playerID3) //etc

效果很好，谢谢——我只想做两个调整：

I d like to return the name of the scorer as well as the player names. Since m.scorer is an ID, how do I map it to the p.Name attribute if p.Name is already being mapped to m.playerID?
The above query returns the names of all the players. I will soon be adding a search feature where you search for all matches for a certain player. Is there anyway of excluding that player from the results (seeing as we already know this player was in the match as he was searched for). So pretty much return all matches, and fellow players for a certain player, but exclude the actual searched player from the results. Sorry if this is unclear, let me know and I ll expand on it.

Answer 1

尝试以下操作（假设字段名）。它将显示玩家的姓名、比赛的比赛ID以及他们是否在那场比赛中得分：）。

SELECT p.Name as  Player Name , m.matchId as  Match Id ,
CASE s.scorerId WHEN ISNULL(S.SCORERID,0) THEN  YES  ELSE  NO  END as  Scored? ,
s.name as  Scorer Name  --as requested from comment
FROM match m
INNER JOIN player p ON p.player_id IN (m.playerID1, m.playerID2, m.playerID3)
LEFT JOIN scorer s ON s.matchId = m.matchId
    AND s.player_id IN (m.playerID1, m.playerID2, m.playerID3)

查看此SQLFiddle点击此处

友情链接