mysql - 选择与另一列具有相同属性的一列

Question

大家好!有一个表只包含 3 列('playerID'、'teamID' 和 'yearID')

我只想获得同一个球员在同一个球队的同一年的列。可以看到如下代码:

Select playerID, teamID, yearID
FROM `appearancessmall`
WHERE (teamID IN 
(SELECT teamID from  `appearancessmall` WHERE playerID = 'napolmi01' )  
) AND
(yearID  IN (SELECT yearID from  `appearancessmall` WHERE playerID = 
'napolmi01')
)

但是查询的输出是与球员“napolmi01”具有相同teamID或相同yearID的总列，我想要的是选择的列应该同时满足这两个要求。那么该怎么做呢？

Answer 1

尝试像下面的例子那样使用LEFT JOIN

SELECT `et`.* FROM `test` `t`
LEFT JOIN `test` `et`
ON `t`.`teamID`=`et`.`teamID` AND `t`.`yearID`=`et`.`yearID`
WHERE `t`.`playerID`='napolmi01';

在 SQL Fiddle 上试用