http://www.itpub.net/thread-1768915-1-1.html

问题：

CREATE TABLE test(

ob_id VARCHAR(32),

ob_name VARCHAR(32)

);

INSERT INTO test VALUES('A001','A001-a');

INSERT INTO test VALUES('A001','A001-b');

INSERT INTO test VALUES('A001','A001-c');

INSERT INTO test VALUES('A001','A001-d');

INSERT INTO test VALUES('A002','A002-a');

INSERT INTO test VALUES('A002','A002-b');

INSERT INTO test VALUES('A002','A002-c');

INSERT INTO test VALUES('A002','A002-d');

COMMIT;

select * from test;查询结果：

OB_ID	OB_NAME
A001	A001-a
A001	A001-b
A001	A001-c
A001	A001-d
A002	A002-a
A002	A002-b
A002	A002-c
A002	A002-d

如何实现下面的查询结果?：

 

OB_ID	OB_NAME
A001	A001-a
	A001-b
	A001-c
	A001-d
A002	A002-a
	A002-b
	A002-c
	A002-d

实现sql：

--select row_number() over(partition by ob_id order by ob_name) from test;

--row_number方法

select decode(row_number() over(partition by ob_id order by ob_name),

1,

ob_id) ob_id,

ob_name

from test t;

--lag或lead方法

select decode(lag(ob_id) over(partition by ob_id order by ob_name),

ob_id,

null,

ob_id) ob_id,

ob_name

from test t;