参考: select a1.f1,a2.f2,a3.f3,a4.f4 from a a1,a a2,a a3,a a4 where a1.f1=a2.f1 and a1.f1=a3.f1 and a1.f1=a4.f1 and a1.f2=a2.f2 and a1.f2=a3.f2 and a1.f2=a4.f2 and a1.f3=a2.f3 and a1.f3=a3.f3 and a1.f3=a4.f3 and a1.f4<>a2.f4 and a1.f4<>a3.f4 and a1.f4<>a4.f4
from a a1,a a2,a a3,a a4 ?/是什么意思?
create table a (a varchar2(10),b varchar2(10),c varchar2(10),d number); insert into a values('a','b','c',0); insert into a values('a','b','c',1); insert into a values('a','b','c',2); insert into a values('b','b','c',0); insert into a values('a','b','b',0); insert into a values('b','b','c',2); SQL> select * from a;A B C D ---------- ---------- ---------- ---------- a b c 0 a b c 1 a b c 2 b b c 0 a b b 0 b b c 26 rows selected执行: select distinct a1.a,a2.b,a3.c,a4.d from a a1,a a2,a a3,a a4 where a1.a=a2.a and a1.a=a3.a and a1.a=a4.a and a1.b=a2.b and a1.b=a3.b and a1.b=a4.b and a1.c=a2.c and a1.c=a3.c and a1.c=a4.c and a1.d<>a2.d and a1.d<>a3.d and a1.d<>a4.d 结果: A B C D ---------- ---------- ---------- ---------- a b c 0 a b c 1 a b c 2 b b c 0 b b c 2看看是不是你想要得?
这个简单: select * from table t1 where rowid in (select rowid from table t2 where t1.a1=t2.a1 and t1.a2=t2.a2 and t1.a3=t2.a3 ......) 这样就可以把table表中a1,a2,a3中重复记录打出来了,rowid是可以在sql中直接使用的id列
--Reference lynx(lynx) create table a (a varchar2(10),b varchar2(10),c varchar2(10),d number); --Reference lynx(lynx) -- Start here SELECT SrcTable.a,SrcTable.b,SrcTable.c,SrcTable.d FROM a SrcTable, (SELECT a,b,c,COUNT(*) CNT FROM a GROUP BY a,b,c ) DistinctWHERE SrcTable.a = Distinct.a AND SrcTable.b = Distinct.b AND SrcTable.c = Distinct.c AND Distinct.CNT > 1 -- IF NEED(because 'f4值不同')
试试是不是类似于下面的(再都多加一字段即可) select a.* from t_c a,(select b.c_col1,b.c_name from t_c b group by b.c_col1,b.c_name having count(*)>1) c where a.c_col1 = c.c_col1 and a.c_name = c.c_nameminusselect a.* from t_c a,(select b.c_col1,b.c_name,b.c_col3 from t_c b group by b.c_col1,b.c_name ,b.c_col3 having count(*)>1) c where a.c_col1 = c.c_col1 and a.c_name = c.c_name and a.c_col3 = c.c_col3
select distinct a1.* from a a1,a a2 where a1.a=a2.a and a1.b=a2.b and a1.c=a2.c and a1.d<>a2.d
select * from a where f4 in(select f4 from a group by f4)
select * from a where f4 in(select f4 from a where f1=f2 and f2=f3 group by f4) 这个可以吗
SELECT A.F1 , A.F2 , A.F3 , A.F4 FROM T_A A WHERE ( SELECT COUNT ( F4 ) FROM T_A B WHERE B.F1 = A.F1 AND B.F2 = A.F2 AND B.F3 = A.F3 ) > 1
--呵呵,太麻烦了,看看那条删除相同记录的语句就知道了。 --如下表:找出 c1列相同的记录:(可以把 rowid>= 换成其它的试试,比如:> select * from tt t1 where t1.rowid>=(select min(rowid) from tt t2 where t1.c1=t2.c1 group by t2.c1 having count(t2.c1)>1);
select a1.f1,a2.f2,a3.f3,a4.f4
from a a1,a a2,a a3,a a4
where a1.f1=a2.f1
and a1.f1=a3.f1
and a1.f1=a4.f1
and a1.f2=a2.f2
and a1.f2=a3.f2
and a1.f2=a4.f2
and a1.f3=a2.f3
and a1.f3=a3.f3
and a1.f3=a4.f3
and a1.f4<>a2.f4
and a1.f4<>a3.f4
and a1.f4<>a4.f4
?/是什么意思?
insert into a values('a','b','c',0);
insert into a values('a','b','c',1);
insert into a values('a','b','c',2);
insert into a values('b','b','c',0);
insert into a values('a','b','b',0);
insert into a values('b','b','c',2);
SQL> select * from a;A B C D
---------- ---------- ---------- ----------
a b c 0
a b c 1
a b c 2
b b c 0
a b b 0
b b c 26 rows selected执行:
select distinct a1.a,a2.b,a3.c,a4.d
from a a1,a a2,a a3,a a4
where a1.a=a2.a
and a1.a=a3.a
and a1.a=a4.a
and a1.b=a2.b
and a1.b=a3.b
and a1.b=a4.b
and a1.c=a2.c
and a1.c=a3.c
and a1.c=a4.c
and a1.d<>a2.d
and a1.d<>a3.d
and a1.d<>a4.d
结果:
A B C D
---------- ---------- ---------- ----------
a b c 0
a b c 1
a b c 2
b b c 0
b b c 2看看是不是你想要得?
select * from table t1 where rowid in (select rowid from table t2 where
t1.a1=t2.a1 and t1.a2=t2.a2 and t1.a3=t2.a3 ......)
这样就可以把table表中a1,a2,a3中重复记录打出来了,rowid是可以在sql中直接使用的id列
create table a (a varchar2(10),b varchar2(10),c varchar2(10),d number);
--Reference lynx(lynx)
-- Start here
SELECT SrcTable.a,SrcTable.b,SrcTable.c,SrcTable.d FROM
a SrcTable,
(SELECT a,b,c,COUNT(*) CNT
FROM a
GROUP BY a,b,c
) DistinctWHERE
SrcTable.a = Distinct.a
AND SrcTable.b = Distinct.b
AND SrcTable.c = Distinct.c
AND Distinct.CNT > 1 -- IF NEED(because 'f4值不同')
select a.*
from t_c a,(select b.c_col1,b.c_name from t_c b group by b.c_col1,b.c_name having count(*)>1) c
where a.c_col1 = c.c_col1 and a.c_name = c.c_nameminusselect a.*
from t_c a,(select b.c_col1,b.c_name,b.c_col3 from t_c b group by b.c_col1,b.c_name ,b.c_col3 having count(*)>1) c
where a.c_col1 = c.c_col1 and a.c_name = c.c_name and a.c_col3 = c.c_col3
from a a1,a a2
where a1.a=a2.a
and a1.b=a2.b
and a1.c=a2.c
and a1.d<>a2.d
(呵呵,当然,Oracle内存太少肯定慢:)楼主可以试试我前面提供的SQL~~
where f4 in(select f4 from a group by f4)
where f4 in(select f4 from a where f1=f2 and f2=f3 group by f4)
这个可以吗
A.F1 ,
A.F2 ,
A.F3 ,
A.F4
FROM
T_A A
WHERE
(
SELECT
COUNT ( F4 )
FROM
T_A B
WHERE
B.F1 = A.F1
AND B.F2 = A.F2
AND B.F3 = A.F3 ) > 1
--如下表:找出 c1列相同的记录:(可以把 rowid>= 换成其它的试试,比如:>
select * from tt t1 where t1.rowid>=(select min(rowid) from tt t2 where t1.c1=t2.c1 group by t2.c1 having count(t2.c1)>1);