表结构如下:
CREATE TABLE [dbo].[Test](
[Code] [nchar](10) NULL,
[name] [nvarchar](50) NULL,
[amout] [numeric](18, 0) NULL
) ON [PRIMARY]
有如下数据:
Code name amout
---------- -------------------------------------------------- ---------------------------------------
apple 苹果 1
apple 苹果 -1
sheep 羊 12
sheep 羊 -12
apple 苹果 1
apple 苹果 1
apple 苹果 -1
sheep 羊 12
sheep 羊 12(9 行受影响)
大家请看上面的数据我要去掉这种数据:amount的值是正负抵消,但是code和name的值是相同的,例如:
apple 苹果 1
apple 苹果 -1请问怎么用sql语句查询出最终的结果应该是
Code name amout
---------- -------------------------------------------------- ---------------------------------------
apple 苹果 1
sheep 羊 12
sheep 羊 12
CREATE TABLE [dbo].[Test](
[Code] [nchar](10) NULL,
[name] [nvarchar](50) NULL,
[amout] [numeric](18, 0) NULL
) ON [PRIMARY]
有如下数据:
Code name amout
---------- -------------------------------------------------- ---------------------------------------
apple 苹果 1
apple 苹果 -1
sheep 羊 12
sheep 羊 -12
apple 苹果 1
apple 苹果 1
apple 苹果 -1
sheep 羊 12
sheep 羊 12(9 行受影响)
大家请看上面的数据我要去掉这种数据:amount的值是正负抵消,但是code和name的值是相同的,例如:
apple 苹果 1
apple 苹果 -1请问怎么用sql语句查询出最终的结果应该是
Code name amout
---------- -------------------------------------------------- ---------------------------------------
apple 苹果 1
sheep 羊 12
sheep 羊 12
from tb group by Code , name
if object_id('Test') is not null drop table Test
go
CREATE TABLE [dbo].[Test](
[Code] [nchar](10) NULL,
[name] [nvarchar](50) NULL,
[amout] [numeric](18, 0) NULL
) ON [PRIMARY]
go
insert into Test
select 'apple',N'苹果',1 union all
select 'apple',N'苹果',-1 union all
select 'sheep',N'羊',12 union all
select 'sheep',N'羊',-12 union all
select 'apple',N'苹果',1 union all
select 'apple',N'苹果',1 union all
select 'apple',N'苹果',-1 union all
select 'sheep',N'羊',12 union all
select 'sheep',N'羊',12
go
select t.Code,t.name,t.amout from Test t
where exists(select 1 from Test where t.Code=Code and t.name=name and t.amout=-amout) and t.amout>0
group by t.Code,t.name,t.amout/*(9 行受影响)
Code name amout
---------- -------------------------------------------------- ---------------------------------------
apple 苹果 1
sheep 羊 12(2 行受影响)
*/
if object_id('Test') is not null drop table Test
go
CREATE TABLE [dbo].[Test](
[Code] [nchar](10) NULL,
[name] [nvarchar](50) NULL,
[amout] [numeric](18, 0) NULL
) ON [PRIMARY]
go
insert into Test
select 'apple',N'苹果',1 union all
select 'apple',N'苹果',-1 union all
select 'sheep',N'羊',12 union all
select 'sheep',N'羊',-12 union all
select 'apple',N'苹果',1 union all
select 'apple',N'苹果',1 union all
select 'apple',N'苹果',-1 union all
select 'sheep',N'羊',12 union all
select 'sheep',N'羊',12
go
;with cte as
(
select row_number() over(partition by Code,name,amout order by code) rn,t.Code,t.name,t.amout from Test t
where exists(select 1 from Test where t.Code=Code and t.name=name and t.amout=-amout)
)
select Code,name,amout from cte t
where not exists(select 1 from cte where t.rn=rn and t.Code=Code and t.name=name and t.amout=-amout)
/*(9 行受影响)
Code name amout
---------- -------------------------------------------------- ---------------------------------------
apple 苹果 1
sheep 羊 12
sheep 羊 12(3 行受影响)*/
--> 测试数据:[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([Code] VARCHAR(5),[name] VARCHAR(4),[amout] INT)
INSERT [tb]
SELECT 'apple','苹果',1 UNION ALL
SELECT 'apple','苹果',-1 UNION ALL
SELECT 'sheep','羊',12 UNION ALL
SELECT 'sheep','羊',-12 UNION ALL
SELECT 'apple','苹果',1 UNION ALL
SELECT 'apple','苹果',1 UNION ALL
SELECT 'apple','苹果',-1 UNION ALL
SELECT 'sheep','羊',12 UNION ALL
SELECT 'sheep','羊',12
--------------开始查询--------------------------SELECT *,id=IDENTITY(INT,1,1) INTO #t FROM tb
SELECT [Code],[name],[amout],cnt=(select count([amout]) FROM #t WHERE [Code]=t.[Code] AND [name]=t.[name]
AND [amout] = t.[amout] AND id>=t.id)
INTO #t2
FROM #t AS tSELECT [Code],[name],[amout] FROM #t2 AS t WHERE NOT EXISTS(SELECT 1 FROM #t2 WHERE [Code]=t.[Code] AND [name]=t.[name]
AND [amout] != t.[amout] AND cnt=t.cnt )
----------------结果----------------------------
/*
Code name amout
----- ---- -----------
apple 苹果 1
sheep 羊 12
sheep 羊 12(3 行受影响)*/