select * from [Order] t where exists(select 1 from [Order] where orderId=t.orderId group by orderId having count(*)=1)
select * from Order where orderId<>100100 and (( amount<>-100 andqty<>-1) or (amount<>100 andqty<>1))
select * from Order group by orderId,amount,qty having count(*)=1
select * from tb t where not exists(select 1 from tb where (orderId=t.orderId and t.amount<>amount) or (orderId=t.orderId and amount<>t.amount))
select * from Order a where not exists(select 1 from Order where a.orderId=orderId group by orderId having count(*)>=2)
select orderId,max(amount),max(qty) from OrderId group by orderId having count(*)=1
if object_id('[Order]') is not null drop table [Order] go create table [Order] (orderId int,amount int,qty int) insert into [Order] select 100100,-100,-1 union all select 100100,100,1 union all select 100102,200,2 select * from [Order] t where exists(select 1 from [Order] where orderId=t.orderId group by orderId having count(*)=1) /* orderId amount qty ----------- ----------- ----------- 100102 200 2 */
-------------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-04-02 14:56:53 -- 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) -------------------------------------------------------------------------- --> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb] GO CREATE TABLE [tb]([orderId] INT,[amount] INT,[qty] INT) INSERT [tb] SELECT 100100,-100,-1 UNION ALL --去 SELECT 100100,100,1 UNION ALL --去 SELECT 100101,100,1 --留 GO --SELECT * FROM [tb]-->SQL查询如下: select * from tb t where not exists( select 1 from tb where (orderId=t.orderId and t.amount<>amount) or (orderId=t.orderId and amount<>t.amount)) /* orderId amount qty ----------- ----------- ----------- 100101 100 1(1 行受影响) */
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2010-04-02 14:59:44 -- 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,1010102,100,1 union all select 100100,1010103,100,1 --------------开始查询-------------------------- select * from tb where productID not in(select productID from tb group by productID having count(1)>1) ----------------结果---------------------------- /* orderId productID amount qty ----------- ----------- ----------- ----------- 100100 1010102 100 1 100100 1010103 100 1(2 行受影响)*/
---------------------------------------------------------------- -- 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 行受影响)*/
select distinct * from [order] t where t.amount>0
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 行受影响)
from [Order] t
where exists(select 1
from [Order]
where orderId=t.orderId
group by orderId having count(*)=1)
where orderId<>100100 and (( amount<>-100 andqty<>-1)
or (amount<>100 andqty<>1))
where not exists(select 1 from Order where a.orderId=orderId group by orderId having count(*)>=2)
go
create table [Order] (orderId int,amount int,qty int)
insert into [Order]
select 100100,-100,-1 union all
select 100100,100,1 union all
select 100102,200,2
select *
from [Order] t
where exists(select 1
from [Order]
where orderId=t.orderId
group by orderId having count(*)=1)
/*
orderId amount qty
----------- ----------- -----------
100102 200 2
*/
-- Author : htl258(Tony)
-- Date : 2010-04-02 14:56:53
-- 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)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([orderId] INT,[amount] INT,[qty] INT)
INSERT [tb]
SELECT 100100,-100,-1 UNION ALL --去
SELECT 100100,100,1 UNION ALL --去
SELECT 100101,100,1 --留
GO
--SELECT * FROM [tb]-->SQL查询如下:
select * from tb t
where not exists(
select 1 from tb
where (orderId=t.orderId and t.amount<>amount)
or (orderId=t.orderId and amount<>t.amount))
/*
orderId amount qty
----------- ----------- -----------
100101 100 1(1 行受影响)
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-02 14:59:44
-- 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,1010102,100,1 union all
select 100100,1010103,100,1
--------------开始查询--------------------------
select * from tb where productID not in(select productID from tb group by productID having count(1)>1)
----------------结果----------------------------
/* orderId productID amount qty
----------- ----------- ----------- -----------
100100 1010102 100 1
100100 1010103 100 1(2 行受影响)*/
-- 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
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 行受影响)