110,545
社区成员
发帖
与我相关
我的任务
分享
select t.*
from mytable t,
(select A, COUNT(*) as num
from mytable
where C = 8
and B = 7
group by A) t1
where t.A = t1.A
and t1.num <= 3
and t.C = 8
and t.B = 7
select t1.* from (select t.* from
mytable t where t.C=8 and t.A in (select A from mytable where A=t.A and B=7)
and t.B=7 order by t.A) t1
where rownum <= 3
order by rownum
Create table mytable
(
id VARCHAR2(10) not null,
A int,
B int,
C int
);
insert into mytable
select 'ab',3,7,8 from dual
union all
select 'ab',2,7,9 from dual
union all
select 'ab',1,7,8 from dual
union all
select 'ab',1,8,8 from dual
union all
select 'ab',2,7,9 from dual
union all
select 'ab',2,7,8 from dual
union all
select 'ab',1,7,8 from dual
select t.* from
mytable t where t.C=8 and t.A in (select A from mytable where A=t.A and B=7)
and t.B=7
select *
from table1
where C=8 and B=7 and A in (select A from (select count(*) as num,A from Table1 where B=7 group by A) z where z.num <= 3)
select A.* from AA A right join (select AA.A,COUNT(*) as num from AA where AA.C=8 and AA.B=7 group by AA.A) B
on A.A=B.A where B.num<=3 and A.C=8 and A.B=7