代码之家 › 专栏 › 技术社区 › Doonie Darkoo

如何在select中排除相同的行

sql-server

Doonie Darkoo · 技术社区 · 7 年前

我有一个创建表和存储销售订单信息的脚本。它背后的场景是,一旦将项目添加到销售订单中,它将 Status 是 'A' 方法 Add . 稍后,客户希望删除该项目,因此我们添加了一个具有相同详细信息的新行,但 状态 作为 'D' 方法 Delete . 现在我只想得到活跃的销售订单项目,不应该包括 Added 然后 Removed 从秩序。这是我的剧本。

CREATE TABLE [dbo].[SALE_DETAIL](
    [ORDER_NUMBER] [varchar](50) NULL,
    [ITEM_NAME] [varchar](250) NULL,
    [QUANTITY] [int] NULL,
    [PRICE] [numeric](18, 0) NULL,
    [Status] [varchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[SALE_DETAIL] ([ORDER_NUMBER], [ITEM_NAME], [QUANTITY], [PRICE], [Status]) VALUES (N'SO-100-ORD-19', N'Double Bed', 5, CAST(70000 AS Numeric(18, 0)), N'A')
GO
INSERT [dbo].[SALE_DETAIL] ([ORDER_NUMBER], [ITEM_NAME], [QUANTITY], [PRICE], [Status]) VALUES (N'SO-100-ORD-19', N'Sofa', 5, CAST(10000 AS Numeric(18, 0)), N'A')
GO
INSERT [dbo].[SALE_DETAIL] ([ORDER_NUMBER], [ITEM_NAME], [QUANTITY], [PRICE], [Status]) VALUES (N'SO-100-ORD-19', N'Dining Table', 1, CAST(50000 AS Numeric(18, 0)), N'A')
GO
INSERT [dbo].[SALE_DETAIL] ([ORDER_NUMBER], [ITEM_NAME], [QUANTITY], [PRICE], [Status]) VALUES (N'SO-100-ORD-19', N'Sofa', 5, CAST(10000 AS Numeric(18, 0)), N'D')   
GO

我要查找的预期输出应该是这样的项目 'Sofa' 已从订单中取消。

ORDER_NUMBER    ITEM_NAME       QTY PRICE
SO-100-ORD-19   Dining Table    1   50000
SO-100-ORD-19   Double Bed      5   70000

查询:

SELECT ORDER_NUMBER, ITEM_NAME, QUANTITY, PRICE FROM [dbo].[SALE_DETAIL]
WHERE Status <> 'D'
GROUP BY ORDER_NUMBER, ITEM_NAME, QUANTITY, PRICE

4 回复 | 直到 7 年前

Larnu 7 年前

我会用 NOT EXISTS :

SELECT SD.ORDER_NUMBER,
       SD.ITEM_NAME,
       SD.QUANTITY,
       SD.PRICE
FROM dbo.[SALE_DETAIL] SD
WHERE NOT EXISTS (SELECT 1
                  FROM dbo.[SALE_DETAIL] e
                  WHERE e.ORDER_NUMBER = SD.ORDER_NUMBER
                    AND e.ITEM_NAME = SD.ITEM_NAME
                    AND e.[Status] = 'D');

Damien_The_Unbeliever 7 年前

逻辑上,基于集合的答案是使用 EXCEPT :

declare @SALE_DETAIL table([ORDER_NUMBER] [varchar](50) NULL,
    [ITEM_NAME] [varchar](250) NULL,
    [QUANTITY] [int] NULL,[PRICE] [numeric](18, 0) NULL,[Status] [varchar](50) NULL)
INSERT @SALE_DETAIL ([ORDER_NUMBER], [ITEM_NAME], [QUANTITY], [PRICE], [Status]) VALUES
(N'SO-100-ORD-19', N'Double Bed', 5, CAST(70000 AS Numeric(18, 0)), N'A'),
(N'SO-100-ORD-19', N'Sofa', 5, CAST(10000 AS Numeric(18, 0)), N'A'),
(N'SO-100-ORD-19', N'Dining Table', 1, CAST(50000 AS Numeric(18, 0)), N'A'),
(N'SO-100-ORD-19', N'Sofa', 5, CAST(10000 AS Numeric(18, 0)), N'D')

select Order_number,Item_name,Quantity,Price
from @SALE_DETAIL
where Status = 'A'
except
select Order_number,Item_name,Quantity,Price
from @SALE_DETAIL
where Status = 'D'

这会产生你要求的结果。然而,请注意,由于各种原因,这通常在实践中表现不佳,在这种情况下,类似于 Larnu's Answer 可能是首选。

Larnu 7 年前

大写:全选 “A” -没有匹配的记录 “D” -记录存在。

在SQL中:

SELECT ORDER_NUMBER, ITEM_NAME, QUANTITY, PRICE
FROM [dbo].[SALE_DETAIL] X
WHERE Status = 'A'
AND NOT EXISTS (
  SELECT 1
  FROM [dbo].[SALE_DETAIL] Y
  WHERE Y.Status       = 'D'
  AND   Y.ORDER_NUMBER = X.ORDER_NUMBER
  AND   Y.ITEM_NAME    = X.ITEM_NAME
)

Suraj Kumar zip 7 年前

您可以尝试使用 not in 操作员如下所示。

SELECT DISTINCT A.ORDER_NUMBER, A.ITEM_NAME,A.QUANTITY, A.PRICE, A.Status
FROM SALE_DETAIL A
    where A.ITEM_NAME not in (select s.ITEM_NAME from SALE_DETAIL s
    where s.[Status] = 'D')

输出如下

ORDER_NUMBER    ITEM_NAME   QUANTITY    PRICE   Status
------------------------------------------------------
SO-100-ORD-19   Dining Table    1       50000   A       
SO-100-ORD-19   Double Bed      5       70000   A

你可以找到现场演示 Live Demo Here