如何选择另一列中不匹配的数据

问题描述

所以我有这个表A:

color        ID       MODEL
-----------------------------
red        | 10   |   HONDA
blue       | 10   |   TOYOTA
red        | 15   |   ISUZU
red        | 30   |   MITSUBISHI
red        | 5    |   HONDA
blue       | 5    |   SUBARU
orange     | 10   |   HYUNDAI
black      | 40   |   CHRYSLER

我想获得所有红色和蓝色，它们的ID互不相同

i'd like to get all red and blue that does not have the same id with each other

所以我的预期结果是:

color  ID
------------
red  | 15
red  | 30

推荐答案

或使用anti join

select t1.color, t1.id 
from 
    tableA t1
left outer join 
     tableA t2 on t2.id = t1.id and t2.color != t1.color 
where 
 t1.color in ('red', 'blue')
and t2.color is null

这篇关于如何选择另一列中不匹配的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！