查询时过滤特殊重复行

tb表测试数据如下:

orderId productID amount qty
100100 1010101 -100 -1
100100 1010101 100 1
100100 1010101 100 1
100100 1010102 100 1
100100 1010102 -100 -1
100100 1010102 100 1
100100 1010103 100 1
100101 1010101 100 1
100101 1010102 100 1
100101 1010102 -100 -1
100101 1010102 100 1
100101 1010103 100 1

  想查询时,当orderId,productID相同时,过滤掉qty的一正一负的数据,其余保留,得到的效果如下:

orderId     productID   amount      qty
----------- ----------- ----------- -----------
100100      1010101     100         1
100100      1010102     100         1
100100      1010103     100         1
100101      1010101     100         1
100101      1010102     100         1
100101      1010103     100         1

 

--处理过程如下:

--------------------------------------------------------------------------

--  Author : htl258(Tony)

--  Date   : 2010-04-02 17:13:23

--  Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)

--          Mar 29 2009 10:27:29

--          Copyright (c) 1988-2008 Microsoft Corporation

--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

--------------------------------------------------------------------------

--> 生成测试数据表:tb

 

IF NOT OBJECT_ID('[tb]') IS NULL

    DROP TABLE [tb]

GO

CREATE TABLE [tb]([orderId] INT,[productID] INT,[amount] INT,[qty] INT)

INSERT [tb]

SELECT 100100,1010101,-100,-1 UNION ALL

SELECT 100100,1010101,100,1 UNION ALL

SELECT 100100,1010101,100,1 UNION ALL

SELECT 100100,1010102,100,1 UNION ALL

SELECT 100100,1010102,-100,-1 UNION ALL

SELECT 100100,1010102,100,1 UNION ALL

SELECT 100100,1010103,100,1 UNION ALL

SELECT 100101,1010101,100,1 UNION ALL

SELECT 100101,1010102,100,1 UNION ALL

SELECT 100101,1010102,-100,-1 UNION ALL

SELECT 100101,1010102,100,1 UNION ALL

SELECT 100101,1010103,100,1

GO

--SELECT * FROM [tb]

 

-->SQL查询如下:

 

;WITH T AS

(

    SELECT RN=ROW_NUMBER()OVER(ORDER BY ORDERID,PRODUCTID,AMOUNT),*

    FROM TB

)

SELECT orderId,productID,amount,qty

FROM T A

WHERE NOT EXISTS(

    SELECT 1 FROM T

    WHERE ([ORDERID]=A.[ORDERID] AND [PRODUCTID]=A.[PRODUCTID]

       AND (ABS(RN-A.RN)=1 AND (A.QTY+QTY=0))))

/*

orderId     productID   amount      qty

----------- ----------- ----------- -----------

100100      1010101     100         1

100100      1010102     100         1

100100      1010103     100         1

100101      1010101     100         1

100101      1010102     100         1

100101      1010103     100         1

 

(6 行受影响)

*/