oracle如何去列的重复,oracle重复列只显示一次的实现

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;

查询结果：

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

A002A002-a

A002-b

A002-c

A002-d

方法一：

select

decode(rownum,1,ob_id,5,ob_id) ob_id,ob_name from test;

方法二：

select

decode(row_number() over(partition by ob_id order by

ob_name),1,ob_id) ob_id,ob_name from test t;

方法三：

select

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

null, ob_name) ob_id,ob_name from test;

方法四：

SELECT

CASE WHEN m.rn=m.rn1 THEN NULL ELSE m.ob_id END ob_id,

m.ob_name

FROM

(SELECT

d.ob_id,d.ob_name,rn,LAG(d.rn) OVER(ORDER BY d.ob_id) rn1

FROM

(SELECT

t.ob_id,t.ob_name,dense_RANK() OVER( ORDER BY t.ob_id) rn

FROM TEST

t) d) m;

方法五：

select

decode(t1.ob_name, v.name, t1.ob_id, ''), t1.ob_name

from (select * from test t where rowid in (select min(rowid) from

test group

by ob_id,ob_name)) t1

join (select t.ob_id id, min(t.ob_name) name from test t group by

t.ob_id) v on v.id = t1.ob_id

order by

t1.ob_id, t1.ob_name;

方法六：

在sqlplus中

break on

ob_id skip

select *

from test;