過濾掉同一列中具有不同值的相同id的記錄

誰能幫我解決以下問題。

create table [order1] (order_id int, CartTransaction_Id int, Status_Id int);
insert into [order1] values
(357488,87214,1),
(357489,87214,8),
(357490,87214,1),
(357491,87214,1),
(343980,87216,1),
(357483,87216,1);

create table [Status1] (Type VARCHAR(100), Status_Id int);
insert into [Status1] values
('New','1'),
('Awaiting Approval','8'),
('New','1'),
('New','1'),
('New','1'),
('New','1');

   select
A.Order_Id, A.CartTransaction_Id, A.Status_Id, B.Type, B.Status_Id
FROM order1 A
JOIN dbo.Status1 B ON dbo.A.Status_Id = dbo.B.Status_Id
--AND B.Type = 'New' 
order by A.CartTransaction_Id

下面是我從上面的查詢中得到的輸出。

以下是預期的結果

僅當 CartTransaction_Id 組中所有 order_id 的狀態類型為“新”時，我才需要填充數據

從捐贈中，我需要過濾掉 CartTransaction_Id (87214)，因為 order_id status_type 之一是在等待批准

例如，您可以這樣做：

select
A.Order_Id, A.CartTransaction_Id, A.Status_Id, B.Type, B.Status_Id
FROM order1 A
JOIN dbo.Status1 B ON dbo.A.Status_Id = dbo.B.Status_Id
where
not exists (select 1 from order1 o1 where o1.CartTransaction_Id = A.CartTransaction_Id and o1.Status_Id != 1)
order by A.CartTransaction_Id

如果您有很多數據，那麼您可能需要檢查其他替代方案，例如 max + over

試試這個：

SELECT  A.Order_Id, A.CartTransaction_Id, A.Status_Id, B.Type, B.Status_Id
FROM    order1 A
JOIN    Status1 B
   ON A.Status_Id = B.Status_Id
OUTER APPLY (SELECT TOP 1 C.CartTransaction_Id FROM order1 C WHERE A.CartTransaction_Id = C.CartTransaction_Id AND C.Status_Id <> 1) C
WHERE   C.CartTransaction_Id IS NULL
ORDER BY A.CartTransaction_Id

引用自：https://dba.stackexchange.com/questions/215518