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 */
id title
1 3
2 -3
3 4
4 5
5 -4
把id为1,2,3,5的删掉,懂?
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
*/