针对以下情况选择语句

Question

|   Column id         |     status       |
|          1          |      Accept      |
|          1          |     Shipped      |
|          1          |     Received     |
|          2          |      Accept      |
|          2          |     Received     |
|          3          |      Accept      |
|          3          |     Shipped      |
|          3          |     Received     |

我希望输出中那些状态尚未发货但已收到 ID 的 ID。 (未发货但已收到((编号 2(

Answer 1

希望这有帮助：

select column_id
from table
where status = 'Received'
minus
select column_id
from table
where status = 'Shipped'

Answer 2

这是获取id和相应status的另一种方法

select *
from
(select id ,listagg(status,',') within group(order by id) status_concat 
from table
group by id) CTE
where CTE.status_concat='Accept,Received';

如果不需要硬编码并且状态只有这 3('Accept','Received','Shipped')，下面的查询也可以工作

select id
from
(select count(*) CNT,id  from table 
group by id) CTE
where CTE.CNT=2;