新手请教条oracle下的sql查询语句

[newbieeee]

表info的基本结构和数据如下，其中sid（小类）和bid（大类）有从属关系，即sid相同的bid必定相同
ID CONTENT BID SID
-----------------------------------
1 1 内容1 1 1
2 2 内容2 1 1
3 3 内容3 1 2
4 4 内容4 2 3
5 5 内容5 1 4
6 6 内容6 2 3
7 7 内容7 2 5

按以下条件查询，sid相同的只显示一条，bid相同的靠在一起，即希望的结果如下

ID CONTENT BID SID
-----------------------------------
1 1 内容1 1 1
2 3 内容3 1 2
3 5 内容5 1 4
4 4 内容4 2 3
5 7 内容7 2 5

请问应该如何写sql语句？对oracle不熟，请各位指教了，多谢；


测试数据对应的SQL语句

create table info(id varchar(5), content varchar(100), bid varchar(5), sid varchar(5));
insert into info values('1', '内容1', '1', '1');
insert into info values('2', '内容2', '1', '1');
insert into info values('3', '内容3', '1', '2');
insert into info values('4', '内容4', '2', '3');
insert into info values('5', '内容5', '1', '4');
insert into info values('6', '内容6', '2', '3');
insert into info values('7', '内容7', '2', '5');

[shiyiwan]

select * from
(select t.*, row_number() over(partition by bid,sid order by id) rn from info t)
where rn = 1
order by bid;

[zhangwonderful]

1楼方法可行

[samonchang]

xue xi lee

[ACMAIN_CHM]

select rownum,CONTENT,BID,SID
from info a
where not exists (select 1
	from info
	where SID=a.SID and ID<a.ID)
order by id

[dawugui]

SELECT T.* FROM INFO T WHERE ID = (SELECT MIN(ID) FROM INFO WHERE sid = T.sid) ORDER BY T.BID

SELECT T.* FROM INFO T WHERE NOT EXISTS (SELECT MIN(ID) FROM INFO WHERE sid = T.sid AND ID < T.ID ) ORDER BY T.BID

[wh62592855]

[Quote=引用 1 楼 shiyiwan 的回复:]
select * from
(select t.*, row_number() over(partition by bid,sid order by id) rn from info t)
where rn = 1
order by bid;
[/Quote]只partition by sid行不行呢

[shiyiwan]

#6

可以的，我写的太多了，呵呵

[wh62592855]

[lixinbill]

select * from
(select t.*, row_number() over(partition by bid,sid order by id) rn from info t)
where rn = 1
order by bid;

[newbieeee]

多谢各位的热心回复了。结贴