选择具有相同ID但在其他列中不同的值

I've got this table.

我有这张桌子。

OWNS:

Pid Cid
100 1
100 2
200 1
200 3
300 4
400 1
400 4
400 5
500 2
500 4

I need to select only Pid who have Cid = 1 but not Cid = 2 the output should be:

我只需要选择只有Cid = 1而不是Cid = 2的Pid输出应该是:

pid
200
400

My query is:

我的查询是:

SELECT Pid
FROM OWNS 
WHERE Cid = 1 and Cid <> 2

but I get result :

但我得到了结果:

pid
100
200
400

2 个解决方案

#1

Try

SELECT distinct Pid 
FROM OWNS 
WHERE 
   Pid not in (select Pid from OWNS where Cid = 2) 
AND CID = 1;

#2

Or this:

SELECT `pid` 
FROM `OWNS` 
WHERE `cid` = '1' 
    AND `pid` in (SELECT `pid` 
                  FROM `OWNS` 
                  WHERE `cid` != '1' AND `cid` != '2')