--这个意思? create table t( a int, b int, c int, d varchar2(20) ); insert into t values(1,1,1,'a'); insert into t values(1,1,1,'b'); insert into t values(1,1,1,'c'); insert into t values(1,1,2,'d'); insert into t values(1,1,2,'e');select t.a,t.b,t.c,t.d from t,( select a,b,c from t group by a,b,c having count(1)>=3) t1 where t.a=t1.a and t.b=t1.b and t.c=t1.c;
select t.a,t.b,t.c,t.d from t,( select a,b,c from t group by a,b,c having count(1)>=3) t1 where t.a=t1.a and t.b=t1.b and t.c=t1.c;因为t是张大表,所以想看看有没什么oracle特殊实现方式
个人认为大表少用连接.. 这种方式是否更优化一些 SELECT * FROM ( SELECT t.*,Count(*) over (PARTITION BY a,b,c)num FROM t ) WHERE num>=3;
--这个意思?
create table t(
a int,
b int,
c int,
d varchar2(20)
);
insert into t values(1,1,1,'a');
insert into t values(1,1,1,'b');
insert into t values(1,1,1,'c');
insert into t values(1,1,2,'d');
insert into t values(1,1,2,'e');select t.a,t.b,t.c,t.d from t,(
select a,b,c from t group by a,b,c having count(1)>=3) t1
where t.a=t1.a and t.b=t1.b and t.c=t1.c;
select a,b,c from t group by a,b,c having count(1)>=3) t1
where t.a=t1.a and t.b=t1.b and t.c=t1.c;因为t是张大表,所以想看看有没什么oracle特殊实现方式
这种方式是否更优化一些
SELECT * FROM
(
SELECT t.*,Count(*) over (PARTITION BY a,b,c)num FROM t
)
WHERE num>=3;