外面再套一层 with t (id,name,pass,role,age) as (select 1,'adf','234',1,21 from dual union all select 2,'fda','qql',1,21 from dual union all select 3,'adf1','234',2,21 from dual union all select 4,'adf2','234',2,21 from dual union all select 5,'adf3','234',2,21 from dual union all select 6,'adf4','244',2,21 from dual union all select 7,'adf5','234',2,21 from dual union all select 8,'adf6','233',2,21 from dual union all select 9,'adf7','222',2,21 from dual union all select 10,'adf8','234',2,21 from dual union all select 11,'adf','234',2,21 from dual union all select 12,'adf','234',2,21 from dual union all select 13,'adf','234',2,21 from dual union all select 14,'adf','234',2,21 from dual ) select * from ( select t.*,row_number()over (partition by t.role order by id) rn from t where t.role =2 --条件 ) tt where tt.rn>=(2-1)*5+1 -- 分页(5个一组) i=2 第二页 and tt.rn <= 2*5
select * from (select rownum rn, e1.* from emp e1 where e1.deptno=20) where rn between 1 and 5;我是根据我现存的表写的分页,你看看,行不
楼主的意思是不是只要求某字段的各个值的个数?? 如果是这样的话,我觉得应该是SELECT role,count(*) FROM table_name GROUP BY role WHERE ROWNUMBER BETWEEN 1 AND 5;
with t (id,name,pass,role,age)
as
(select 1,'adf','234',1,21 from dual union all
select 2,'fda','qql',1,21 from dual union all
select 3,'adf1','234',2,21 from dual union all
select 4,'adf2','234',2,21 from dual union all
select 5,'adf3','234',2,21 from dual union all
select 6,'adf4','244',2,21 from dual union all
select 7,'adf5','234',2,21 from dual union all
select 8,'adf6','233',2,21 from dual union all
select 9,'adf7','222',2,21 from dual union all
select 10,'adf8','234',2,21 from dual union all
select 11,'adf','234',2,21 from dual union all
select 12,'adf','234',2,21 from dual union all
select 13,'adf','234',2,21 from dual union all
select 14,'adf','234',2,21 from dual
)
select * from (
select t.*,row_number()over (partition by t.role order by id) rn from t
where t.role =2 --条件
) tt
where tt.rn>=(2-1)*5+1 -- 分页(5个一组) i=2 第二页
and tt.rn <= 2*5
如果是这样的话,我觉得应该是SELECT role,count(*) FROM table_name GROUP BY role WHERE ROWNUMBER BETWEEN 1 AND 5;