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;

巴特西

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

最新文章

热门文章