从审计表中获取数据的SQL查询，其中列匹配一个值，但不匹配其他值

Question

我有一个表t_audit，有4列:名称，地址，更新(Y或N)和user_id(更新它的用户的ID)。

我想搜索审计表，获取user_id 1更新的客户名，而不是user_id 2。

我试过了:

select ta.name 
from t_audit ta 
where (ta.updated = 'Y' and user_id = 1 ) 
and not exists (ta.updated='Y' and user_id = 2)

但是它不起作用。我该如何解决这个问题?

Answer 1

for Oracle

select ta.name 
from t_audit ta 
where (ta.updated = 'Y' and user_id = 1 ) 
minus
select ta.name 
from t_audit ta 
where (ta.updated = 'Y' and user_id = 2 ) 

为别人

select ta.name 
from t_audit ta 
where (ta.updated = 'Y' and user_id = 1 ) 
EXCEPT
select ta.name 
from t_audit ta 
where (ta.updated = 'Y' and user_id = 2 ) 

Answer 2

你的not-exists语法是不正确的，下面的代码当然是未经测试的，应该是接近你所期望的

select ta.name 
from t_audit ta 
where ta.updated = 'Y' and ta.user_id = 1  
and not exists (
select * from t_audit ta2
where ta2.name=ta.name and ta2.updated='Y' and ta2.user_id = 2
)