仅返回符合特定条件的值

Question

我有

ID   Rank

11   1  
11   2  
22   1  
22   2  
22   3  
33   1  
33   3  
44   1  
55   1  
55   2  

I Want

11  
44  
55  

即，我只想返回从未处于排名“3”的 ID。

更新 - 除了下面的答案之外，我还想出了另一种更简单的方法来做到这一点。

SELECT ID FROM
(  
  SELECT ID, SUM(CASE WHEN Rank = 3 THEN 1 ELSE 0 END) flag FROM 
  (  
    SELECT ID, Rank FROM T1
  ) a  
  GROUP BY ID    
) b  
WHERE flag = 0;  

Answer 1

使用NOT EXISTS：

SELECT DISTINCT
    yt1.ID
FROM
    yourTable yt1
WHERE
    NOT EXISTS (
        SELECT *
        FROM yourTable yt2
        WHERE 
            yt1.ID = yt2.ID 
            AND yt2.Rank = 3
    )

或者，使用LEFT JOIN：

SELECT DISTINCT
    yt1.ID
FROM
    yourTable yt1
    LEFT JOIN yourTable yt2 ON yt1.ID = yt2.ID AND yt2.Rank = 3
WHERE
    yt2.ID IS NULL

Answer 2

我会创建一组唯一的 ID，然后检查这些 ID 的排名是否为 3。那些没有的，将它们放入最终数组中。

$idArray = array();
$noThreeArray = array();

$query = 'SELECT DISTINCT ID FROM yourTable';
while($row = mysql_fetch_array($query)){
   $idArray[] = $row['ID'];
}
foreach($idArray as $id){
   $query = 'SELECT ID FROM yourTable WHERE ID = $id AND Rank = 3';
   if(mysql_num_rows($query) == 0){
      $noThreeArray[] = $row['ID'];
   }
 }

$noThreeArray 现在具有您想要的值。

Answer 3

SELECT ID FROM
(  
  SELECT ID, SUM(CASE WHEN Rank = 3 THEN 1 ELSE 0 END) flag FROM 
  (  
    SELECT ID, Rank FROM T1
  ) a  
  GROUP BY ID    
) b  
WHERE flag = 0;  

Answer 4

select ID as IDS from table where rank != 3 group by table.ID;