oracle 列相同编号,Oracle查询结果中：一列中相同的值或一列中重复的值，只显示一次...

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;