主页 / dba / 问题 / 215518
Accepted
heye
Asked: 2018-08-22 11:05:46 +0800 CST

过滤掉同一列中具有不同值的相同id的记录

  • 772

谁能帮我解决以下问题。

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 之一是在等待批准

sql-server partitioning

4 个回答

  1. Best Answer

    例如，您可以这样做：

    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

    • 1

  2. 尝试这个：

    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
    • 1

  3. 您的 Status1 表是否需要有多个“NEW”条目...如果不是下面的查询应该可以工作

    select
A.Order_Id, A.CartTransaction_Id, A.Status_Id, B.Type, B.Status_Id
FROM order1 A
JOIN dbo.Status1 B ON A.Status_Id = B.Status_Id
WHERE NOT EXISTS (SELECT 1 FROM order1 c WHERE c.CartTransaction_Id = 
a.CartTransaction_Id AND c.Status_Id <> 1)
order by A.CartTransaction_Id
    • 0

  4. 首先，您不需要向 [Status1] 表插入那么多

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

    对你的加入来说已经足够了。

    这是我对使用 ROW_NUMBER 得到你想要的答案的看法。

    ;with Orders as (
    select A.Order_Id [OrderID]
    , A.CartTransaction_Id [CartTransactionID]
    , A.Status_Id [OrderStatus]
    , B.Type [StatusType]
    , B.Status_Id [StatusID]
    , ROW_NUMBER() OVER(PARTITION BY A.CartTransaction_Id, A.Status_Id ORDER BY A.CartTransaction_Id, A.Status_Id) RowNo
from order1 a join status1 b on a.status_id = b.status_id
)
select o.OrderID
    , o.CartTransactionID
    , o.OrderStatus
    , o.StatusType
    , o.StatusID 
from orders o
Where 1=1
and o.RowNo = 1
and o.StatusID = 1 --o.StatusType='new'
    • 0

相关问题