create table [order] (orderId int,productID int,amount int,qty int) insert into [order] 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 select * from [order] t where not exists(select 1 from [order] where orderid=t.orderid and t.productID =productID and t.amount*amount<0 ) orderId productID amount qty ----------- ----------- ----------- ----------- 100100 1010103 100 1(1 行受影响)
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2010-04-02 15:38:22 -- Verstion: -- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) -- May 26 2009 14:24:20 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not 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 --------------开始查询-------------------------- select orderId,productID,amount,qty from ( select *,id=row_number()over(partition by productID order by amount) from tb )t where id>=2 ----------------结果---------------------------- /* orderId productID amount qty ----------- ----------- ----------- ----------- 100100 1010101 100 1 100100 1010101 100 1 100100 1010102 100 1 100100 1010102 100 1(4 行受影响)*/
是去掉每一productID的一条正和一条负吧?
select distinct * from [order] t where t.amount>0
with cte as ( select ROW_NUMBER() over(order by orderId,productID,amount) id,orderId ,productID ,amount ,qty from [order] ) select * from cte t1 where not exists(select 1 from cte t2 where t1.orderid=t2.orderid and t1.productid=t2.productid and (t1.id+1=t2.id or t1.id-1=t2.id) and t1.amount*t2.amount<0)id orderId productID amount qty -------------------- ----------- ----------- ----------- ----------- 3 100100 1010101 100 1 6 100100 1010102 100 1 7 100100 1010103 100 1(3 行受影响)
create table [order] (orderId int,productID int,amount int,qty int) insert into [order] 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 with cte as ( select ROW_NUMBER() over(order by orderId,productID,amount) id,orderId ,productID ,amount ,qty from [order] ) select * from cte t1 where not exists(select 1 from cte t2 where t1.orderid=t2.orderid and t1.productid=t2.productid and (t1.id+1=t2.id or t1.id-1=t2.id) and t1.amount*t2.amount<0)id orderId productID amount qty -------------------- ----------- ----------- ----------- ----------- 3 100100 1010101 100 1 6 100100 1010102 100 1 7 100100 1010103 100 1(3 行受影响)
--> 生成测试数据表:tbIF 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 100100,1010103,100,-1 UNION ALL SELECT 100100,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)))) /* rn orderId productID amount qty -------------------- ----------- ----------- ----------- ----------- 3 100100 1010101 100 1 6 100100 1010102 100 1 9 100100 1010103 100 -1(3 行受影响) */
总算是看懂你的问题了,就是把amount 为负数的记录剔除掉。SELECT * FROM [ORDER] AS ord WHERE NOT EXISTS ( SELECT 1 FROM (SELECT * FROM [ORDER] WHERE amount<0) AS ReturnOrder WHERE ord.orderId = ReturnOrder.orderId AND ord.productID = ReturnOrder.productID )不知道你是要抵消还是只要有不满意的就不要此商品?表达清楚点,大哥
select *
from [order] t
where not exists(select 1
from [order] where orderid=t.orderid and t.productID =productID and t.amount*amount<0 )
select *
from [order] t
where not exists(select 1
from [order] where orderid=t.orderid and t.productID =productID and t.amount*amount<0 )
insert into [order] 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
select *
from [order] t
where not exists(select 1
from [order] where orderid=t.orderid and t.productID =productID and t.amount*amount<0 )
orderId productID amount qty
----------- ----------- ----------- -----------
100100 1010103 100 1(1 行受影响)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-02 15:38:22
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
-- May 26 2009 14:24:20
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not 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
--------------开始查询--------------------------
select
orderId,productID,amount,qty
from
(
select
*,id=row_number()over(partition by productID order by amount)
from
tb
)t
where
id>=2
----------------结果----------------------------
/* orderId productID amount qty
----------- ----------- ----------- -----------
100100 1010101 100 1
100100 1010101 100 1
100100 1010102 100 1
100100 1010102 100 1(4 行受影响)*/
from [order] t
where t.amount>0
with cte as
(
select ROW_NUMBER() over(order by orderId,productID,amount) id,orderId ,productID ,amount ,qty
from [order]
)
select *
from cte t1 where
not exists(select 1 from cte t2 where t1.orderid=t2.orderid and t1.productid=t2.productid and (t1.id+1=t2.id or t1.id-1=t2.id) and
t1.amount*t2.amount<0)id orderId productID amount qty
-------------------- ----------- ----------- ----------- -----------
3 100100 1010101 100 1
6 100100 1010102 100 1
7 100100 1010103 100 1(3 行受影响)
insert into [order] 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
with cte as
(
select ROW_NUMBER() over(order by orderId,productID,amount) id,orderId ,productID ,amount ,qty
from [order]
)
select *
from cte t1 where
not exists(select 1 from cte t2 where t1.orderid=t2.orderid and t1.productid=t2.productid and (t1.id+1=t2.id or t1.id-1=t2.id) and
t1.amount*t2.amount<0)id orderId productID amount qty
-------------------- ----------- ----------- ----------- -----------
3 100100 1010101 100 1
6 100100 1010102 100 1
7 100100 1010103 100 1(3 行受影响)
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 100100,1010103,100,-1 UNION ALL
SELECT 100100,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))))
/*
rn orderId productID amount qty
-------------------- ----------- ----------- ----------- -----------
3 100100 1010101 100 1
6 100100 1010102 100 1
9 100100 1010103 100 -1(3 行受影响)
*/
FROM [ORDER] AS ord
WHERE NOT EXISTS
(
SELECT 1
FROM (SELECT * FROM [ORDER] WHERE amount<0) AS ReturnOrder
WHERE ord.orderId = ReturnOrder.orderId
AND ord.productID = ReturnOrder.productID
)不知道你是要抵消还是只要有不满意的就不要此商品?表达清楚点,大哥