mysql - 在mysql中查询以执行not in功能

我在mysql中编写了一个查询，以过滤掉不在userid=1的vendorids中但在userid=5的vendorids中存在的vendorids，但是我没有得到正确的答案，查询如下

select vendorid
from rating_table
where (userid = 5) not in (select vendorid from rating_table where userid = 1)
order by vendorid asc;

谢谢您

最佳答案

您需要检查子查询结果中是否没有vendorid

SELECT vendorid
FROM rating_table
WHERE (userid = 5)
AND vendorid NOT IN
(
    SELECT
        vendorid
    FROM rating_table
    WHERE userid = 1
)
ORDER BY vendorid ASC;