纠结了3个小时的sql查询有关问题，坐等回复

纠结了3个小时的sql查询问题，坐等回复。
表结构如下：

SQL code

CREATE TABLE [dbo].[Test](    [Code] [nchar](10) NULL,    [name] [nvarchar](50) NULL,    [amout] [numeric](18, 0) NULL) ON [PRIMARY]

有如下数据:

SQL code

Code       name                                               amout---------- -------------------------------------------------- ---------------------------------------apple      苹果                                                 1apple      苹果                                                 -1sheep      羊                                                  12sheep      羊                                                  -12apple      苹果                                                 1apple      苹果                                                 1apple      苹果                                                 -1sheep      羊                                                  12sheep      羊                                                  12(9 行受影响)

大家请看上面的数据我要去掉这种数据：amount的值是正负抵消，但是code和name的值是相同的，例如：
apple 苹果1
apple 苹果-1

请问怎么用sql语句查询出最终的结果应该是

SQL code

Code       name                                               amout---------- -------------------------------------------------- ---------------------------------------apple      苹果                                                 1sheep      羊                                                  12sheep      羊                                                  12

[解决办法]

SQL code

if object_id('Test') is not null drop table Testgo CREATE TABLE [dbo].[Test](    [Code] [nchar](10) NULL,    [name] [nvarchar](50) NULL,    [amout] [numeric](18, 0) NULL) ON [PRIMARY]goinsert into Testselect 'apple',N'苹果',1 union allselect 'apple',N'苹果',-1 union allselect 'sheep',N'羊',12 union allselect 'sheep',N'羊',-12 union allselect 'apple',N'苹果',1 union allselect 'apple',N'苹果',1 union allselect 'apple',N'苹果',-1 union allselect 'sheep',N'羊',12 union allselect 'sheep',N'羊',12go;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 twhere 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      苹果                                                 1sheep      羊                                                  12sheep      羊                                                  12(3 行受影响)*/
[解决办法]
2000 需要借助临时表


SQL code
--> 测试数据：[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 ALLSELECT 'apple','苹果',-1 UNION ALLSELECT 'sheep','羊',12 UNION ALLSELECT 'sheep','羊',-12 UNION ALLSELECT 'apple','苹果',1 UNION ALLSELECT 'apple','苹果',1 UNION ALLSELECT 'apple','苹果',-1 UNION ALLSELECT 'sheep','羊',12 UNION ALLSELECT '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 #t2FROM #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 苹果   1sheep 羊    12sheep 羊    12(3 行受影响)*/ 
  

 

查看更多 下一篇