sql - SQL Join 同一张表

Question

Table T
P_ID | Name | Status
1      ABC    Ordered
1      ABC    Processing
1      ABC    Imported
2      PQR    Ordered
2      PQR    Failed
3      LMN    Ordered

预期结果

Table T
P_ID | Name | Status
2      PQR    Ordered
2      PQR    Failed 
3      LMN    Ordered

通过以下查询，我没有得到正确的结果。我想查找所有状态为已订购但状态不为“已导入”的记录。

select c1.P_ID,c1.Name,c2.Status
from T c1, T c2
where c1.P_ID = c2.P_ID
c1.Status="Ordered" and c2.status != "Imported"

使用 Oracle 11g 数据库

标签： sqloracle

Answer 1

select c1.P_ID, c1.Name, c1.Status
from T c1
where c1.Status = 'Ordered' and 
      NOT EXISTS (SELECT 1
                  FROM T c2
                  WHERE c1.P_ID = c2.P_ID and c2.status = 'Imported'
                 );