oracle 重复列只显示一次的实现

最新推荐文章于 2024-08-21 13:45:00 发布

ckth47350

最新推荐文章于 2024-08-21 13:45:00 发布

阅读量648

点赞数

文章标签：数据库

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
A002	A002-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;

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-1670971/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29785807/viewspace-1670971/