求 SQL解决正数和负数抵消的难题

[dsywo]

给一个表，表中有一列字段是有正有负数据，怎么将该表中正数和负数一样的数据删掉，使结果依然相等了


大家快来帮帮忙吧！！

[开启时代]

+5
-5
+3
-2
把+5和-5的记录删掉？

[dsywo]

嗯，这个怎么写？
[Quote=引用 1 楼 的回复:]

+5
-5
+3
-2
把+5和-5的记录删掉？
[/Quote]

[發糞塗牆]

楼主举个例子来看看

[dsywo]

table
id title
1 3
2 -3
3 4
4 5
5 -4
把id为1,2,3,5的删掉，懂？

[發糞塗牆]

查出来了，删除自己写吧。另外你这个要求如果没有多一点的限制，容易出问题，不然如果是奇数的时候不就惨咯

CREATE TABLE test (id INT,title INT )
 INSERT INTO test 
 SELECT 1, 3
 UNION ALL 
 SELECT 2, -3
 UNION ALL 
 SELECT 3, 4
 UNION ALL 
 SELECT 4, 5
 UNION ALL 
 SELECT 5, -4
 
 SELECT aid FROM (SELECT a.id aid,b.id bid,a.title atitle,b.title btitle FROM test a cross JOIN test b ) a
 WHERE atitle+btitle=0
 /*
 aid
 -----------
 2
 1
 5
 3
 
 (4 行受影响)
 */

[汤姆克鲁斯]

似乎这样才对
难点在于有 -5 -5 5 这样奇数个的情况下

--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO 
CREATE TABLE [tb]([id] INT IDENTITY,[col] INT)
INSERT [tb]
SELECT 10 UNION ALL
SELECT 10 UNION ALL
SELECT 10 UNION ALL
SELECT -10 UNION ALL
SELECT 6 UNION ALL
SELECT -8 UNION ALL
SELECT -6 UNION ALL
SELECT -6 UNION ALL
SELECT 5 UNION ALL
SELECT -6
--------------开始查询--------------------------
SELECT col FROM 
(
SELECT [col],row_id=ROW_NUMBER() OVER(PARTITION BY [col] ORDER BY [id])
FROM [tb] WHERE [col]>0
EXCEPT 
SELECT ABS([col]),row_id=ROW_NUMBER() OVER(PARTITION BY [col] ORDER BY [id])
FROM [tb] WHERE [col]<0
UNION ALL 
SELECT [col],row_id=ROW_NUMBER() OVER(PARTITION BY [col] ORDER BY [id])
FROM [tb] WHERE [col]<0
EXCEPT
SELECT -[col],row_id=ROW_NUMBER() OVER(PARTITION BY [col] ORDER BY [id])
FROM [tb] WHERE [col]>0
) AS t 
/*
col
5
10
10
-8
-6
-6
*/

[以学习为目的]

[Quote=引用 6 楼 的回复:]

似乎这样才对
难点在于有 -5 -5 5 这样奇数个的情况下
SQL code
--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT IDENTITY,[col] INT)
INSERT [tb]
SELECT 10 UNION ALL
SELECT 10 ……
[/Quote]

阿汤哥正解！