SELECT T1.COL1, T1.COL2, T1.COL,-t1.col4 FROM (SELECT * FROM TABLE1 T WHERE T.COL4 < 0) T1, (SELECT DISTINCT T.COL2, T, COL4, TABLE1) T2 WHERE T1.COL2 = T2.COL2 AND T1.COL4 = -T2.COL4
没有看懂你这个是什么意思、 但是按照你题目写了下SELECT T3.COL1, T3.COL2, T3.COL3, T3.COL4 FROM (SELECT T.*, ROW_NUMBER() OVER(PARTITION BY T.COL2, T.COL4 ORDER BY T.COL1) AS RN FROM TABLE1 T INNER JOIN (SELECT DISTINCT S.COL2, S.COL4 FROM TABLE1 S WHERE S.COL4 < 0) T2 ON T.COL2 = T2.COL2 AND T.COL4 = -T2.COL4) T3 WHERE T3.RN = 1
table1
col1 col2 col3 col4
110 222 333 -5
111 222 333 -5
112 222 334 5
113 222 335 5
111 223 336 5
111 223 333 -5
111 223 333 15
所以最后一步并不是简单的distinct
FROM (SELECT * FROM TABLE1 T WHERE T.COL4 < 0) T1,
(SELECT DISTINCT T.COL2, T, COL4, TABLE1) T2
WHERE T1.COL2 = T2.COL2
AND T1.COL4 = -T2.COL4
没有看懂你这个是什么意思、
但是按照你题目写了下SELECT T3.COL1, T3.COL2, T3.COL3, T3.COL4
FROM (SELECT T.*,
ROW_NUMBER() OVER(PARTITION BY T.COL2, T.COL4 ORDER BY T.COL1) AS RN
FROM TABLE1 T
INNER JOIN (SELECT DISTINCT S.COL2, S.COL4
FROM TABLE1 S
WHERE S.COL4 < 0) T2
ON T.COL2 = T2.COL2
AND T.COL4 = -T2.COL4) T3
WHERE T3.RN = 1
112 222 334 5
113 222 335 5
111 223 336 5
然后将table3中col2 col4均相同的剔除到只剩一个
112 222 334 5
111 223 336 5
其实我觉得楼主不是很在乎col1和col3的值,如果在乎,像上面这种情况应该保留哪条呢?