表达的不清楚,难道是f1=f2=f3 f1!=f4?
解决方案 »
- sequence为什么程序里面调用nextval之后。。再pl/sql里用curral查不到更新后的当前值。。
- System.Data.OracleClient 需要 Oracle 客户端软件 version 8.1.7 或更高版本。 如何解决?
- 各位老师有人创建过簇吗,我创建时,有一个簇关键字列,他的名称代码 什么含义呀,帮忙
- 十万火急,数据库连接不上,可能是数据库崩溃吧,在线等待
- Oracle10g 简单问题
- 安装数据库时出现的问题
- 初始安装好Oracle8i后,为什么登陆不上Oracle的sql-plus操作呢?快来救我啊!!!
- 字符集问题
- 一个SQL问题
- 请问GET_LINE结尾情况??
- TNS:could not resolve the connect identifier specified
- DATE型字段有空值吗?
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);