用 group by (abs(字段)) having count(字段)<2 试试!我这没ORACLE,没法测试
SELECT file_num FROM test_file group by (abs(file_num)) having count(file_num)<2group by 不是表达式!
哦!group by 不支持函数 试试 select file_num from test_file A where not exists (select file_num from test_file B where abs(A.file_num ) = abs(B.file_num ) )
如果是随机取的话可以如下做:--for example:SQL> select * from test_tab; NUM ---------- 10 10 10 -10 -10 -10 -10 -100 100 100 1000 10412 rows selectedSQL> SQL> select num,sum(sn),num*sum(sn) num from 2 (select sign(num) sn,abs(num) num from test_tab ) 3 group by num 4 / NUM SUM(SN) NUM ---------- ---------- ---------- 10 -1 -10 100 1 100 104 1 104 1000 1 1000
最终的结果就最右边的NUM栏位
简化后如下: select abs(num)*sum(sign(num)) num from test_tab group by abs(num)SQL> select abs(num)*sum(sign(num)) from test_tab 2 group by abs(num) 3 /ABS(NUM)*SUM(SIGN(NUM)) ----------------------- -10 100 104 1000
SQL> SELECT num from test; NUM ---------- 4000 3000 4000 3000 -4000 -4000 -4000 -4000 -3000 -3000 3000 -2000 -2000 2000 200015 rows selectedSELECT SUM(num) num FROM (SELECT num, row_number() over(PARTITION BY abs(num), num ORDER BY rownum) rn FROM test GROUP BY abs(num), rn HAVING SUM(num) <> 0过滤后的数据: NUM ---------- 3000 -4000 -4000
baojianjun(包子) ( ) 信誉:123 Blog 2006-12-29 13:41:33 得分: 0
楼上的有点太多复杂了:)
-------------------------------------呵呵,仔细看,我们结果不一样的:原始数据: SQL> SELECT num from test; NUM ---------- 4000 3000 4000 3000 -4000 -4000 -4000 -4000 -3000 -3000 3000 -2000 -2000 2000 200015 rows selected我的结果:SELECT SUM(num) num FROM (SELECT num, row_number() over(PARTITION BY abs(num), num ORDER BY rownum) rn FROM test GROUP BY abs(num), rn HAVING SUM(num) <> 0 NUM ---------- 3000 -4000 -4000baojianjun(包子) 的结果:select abs(num)*sum(sign(num)) from test_tab group by abs(num) ABS(NUM)*SUM(SIGN(NUM)) ----------------------- 0 3000 -8000
谢谢楼上的各位包子的select abs(num)*sum(sign(num)) from test_tab group by abs(num) 好像有点问题 就是当记录数不只差1条时 好像全部都加起来了。 to duanzilin(寻): SELECT SUM(num) num FROM (SELECT num, row_number() over(PARTITION BY abs(num), num ORDER BY rownum) rn FROM test GROUP BY abs(num), rn HAVING SUM(num) <> 0如果想把记录对应的ID号也取出来?
其他字段,只要用max取就可以了,例如:SELECT SUM(num) num,max(id) id FROM (SELECT num,id, row_number() over(PARTITION BY abs(num), num ORDER BY rownum) rn FROM test GROUP BY abs(num), rn HAVING SUM(num) <> 0
select * from test_table where num not in( select num from ( select num,count(*) as cnt from test_table group by num ) t1 where not exists(select * (select num,count(*) as cnt from test_table group by num) t2 where t1.cnt=t2.cnt and t1.num=-t2.num))
select * from jack where test not in ( Select t1.test from jack t1,jack t2 where t1.test =-t2.test )
SQL> select * from x;A1 ---------- -10 50 20 -10 10 30 20 30 409 rows selectedSQL> select sign(a1)*abs(a1) from x group by a1;SIGN(A1)*ABS(A1) ---------------- -10 10 20 30 40 506 rows selectedSQL>
试试
select file_num
from test_file A
where not exists
(select file_num
from test_file B
where abs(A.file_num ) = abs(B.file_num )
)
----------
10
10
10
-10
-10
-10
-10
-100
100
100
1000
10412 rows selectedSQL>
SQL> select num,sum(sn),num*sum(sn) num from
2 (select sign(num) sn,abs(num) num from test_tab )
3 group by num
4 / NUM SUM(SN) NUM
---------- ---------- ----------
10 -1 -10
100 1 100
104 1 104
1000 1 1000
select abs(num)*sum(sign(num)) num from test_tab
group by abs(num)SQL> select abs(num)*sum(sign(num)) from test_tab
2 group by abs(num)
3 /ABS(NUM)*SUM(SIGN(NUM))
-----------------------
-10
100
104
1000
----------
4000
3000
4000
3000
-4000
-4000
-4000
-4000
-3000
-3000
3000
-2000
-2000
2000
200015 rows selectedSELECT SUM(num) num
FROM (SELECT num,
row_number() over(PARTITION BY abs(num), num ORDER BY rownum) rn
FROM test
GROUP BY abs(num), rn
HAVING SUM(num) <> 0过滤后的数据: NUM
----------
3000
-4000
-4000
楼上的有点太多复杂了:)
-------------------------------------呵呵,仔细看,我们结果不一样的:原始数据:
SQL> SELECT num from test; NUM
----------
4000
3000
4000
3000
-4000
-4000
-4000
-4000
-3000
-3000
3000
-2000
-2000
2000
200015 rows selected我的结果:SELECT SUM(num) num
FROM (SELECT num,
row_number() over(PARTITION BY abs(num), num ORDER BY rownum) rn
FROM test
GROUP BY abs(num), rn
HAVING SUM(num) <> 0
NUM
----------
3000
-4000
-4000baojianjun(包子) 的结果:select abs(num)*sum(sign(num)) from test_tab
group by abs(num)
ABS(NUM)*SUM(SIGN(NUM))
-----------------------
0
3000
-8000
group by abs(num) 好像有点问题 就是当记录数不只差1条时 好像全部都加起来了。
to duanzilin(寻): SELECT SUM(num) num
FROM (SELECT num,
row_number() over(PARTITION BY abs(num), num ORDER BY rownum) rn
FROM test
GROUP BY abs(num), rn
HAVING SUM(num) <> 0如果想把记录对应的ID号也取出来?
FROM (SELECT num,id,
row_number() over(PARTITION BY abs(num), num ORDER BY rownum) rn
FROM test
GROUP BY abs(num), rn
HAVING SUM(num) <> 0
select num from (
select num,count(*) as cnt from test_table
group by num
) t1
where not exists(select *
(select num,count(*) as cnt from test_table
group by num) t2
where t1.cnt=t2.cnt and t1.num=-t2.num))
where test not in
(
Select t1.test
from jack t1,jack t2
where t1.test =-t2.test
)
----------
-10
50
20
-10
10
30
20
30
409 rows selectedSQL> select sign(a1)*abs(a1) from x group by a1;SIGN(A1)*ABS(A1)
----------------
-10
10
20
30
40
506 rows selectedSQL>