WITH CTE AS ( SELECT P.dep , P.NAME,ROWNUM RM FROM PRODUCTS P ) SELECT dep,NAME FROM ( SELECT C.dep,C.NAME, ,c.rm FROM CTE C WHERE C.RM =1 UNION SELECT CASE T.dep WHEN C.dep THEN '' ELSE C.dep END dep, C.NAME,c.rm FROM CTE C , CTE T WHERE C.RM = T.RM +1 ) ORDER BY rm
create table employee( id int, deptno int, name varchar2(20) );insert into employee values(1 , 1 , '张三' ); insert into employee values( 2, 1 , '李四' ); insert into employee values(3 , 2 , '王五' ); insert into employee values(4 , 2 , '赵六' ); insert into employee values(5 , 2 , '李银' ); insert into employee values(6 , 3 , '花擦擦'); insert into employee values(7 , 3 , '葫芦娃'); insert into employee values( 8, 3 , '黑鱼精'); select decode(row_number() over(partition by deptno order by name), --函数处理得到分级内的行号 1, deptno, --只有第1行显示行号 ''), name from employee
AS
(
SELECT P.dep , P.NAME,ROWNUM RM FROM PRODUCTS P
)
SELECT dep,NAME FROM
(
SELECT C.dep,C.NAME, ,c.rm
FROM CTE C WHERE C.RM =1
UNION
SELECT CASE T.dep WHEN C.dep THEN '' ELSE C.dep END dep, C.NAME,c.rm
FROM CTE C , CTE T WHERE C.RM = T.RM +1
)
ORDER BY rm
id int,
deptno int,
name varchar2(20)
);insert into employee values(1 , 1 , '张三' );
insert into employee values( 2, 1 , '李四' );
insert into employee values(3 , 2 , '王五' );
insert into employee values(4 , 2 , '赵六' );
insert into employee values(5 , 2 , '李银' );
insert into employee values(6 , 3 , '花擦擦');
insert into employee values(7 , 3 , '葫芦娃');
insert into employee values( 8, 3 , '黑鱼精');
select decode(row_number() over(partition by deptno order by name), --函数处理得到分级内的行号
1, deptno, --只有第1行显示行号
''),
name
from employee
select 部门,姓名 from 雇员表
order by 部门;