有如下表结构:
ID name
1 aaaa
1 bbbb
1 cccc
2 dddd
2 eeee
2 ffff
3 gggg
4 gggg
4 gggg
5 gggg
5 gggg
5 gggg
要求:用一条SQL写出取不重复的ID的前两条数据,如果只有一条,就取一条。得到结果应该是:
1 aaaa
1 bbbb
2 dddd
2 eeee
3 gggg
4 gggg
4 gggg
5 gggg
5 gggg
今天一兄弟问我,愣了
ID name
1 aaaa
1 bbbb
1 cccc
2 dddd
2 eeee
2 ffff
3 gggg
4 gggg
4 gggg
5 gggg
5 gggg
5 gggg
要求:用一条SQL写出取不重复的ID的前两条数据,如果只有一条,就取一条。得到结果应该是:
1 aaaa
1 bbbb
2 dddd
2 eeee
3 gggg
4 gggg
4 gggg
5 gggg
5 gggg
今天一兄弟问我,愣了
解决方案 »
- 【在线等!!!】SQL2005使用excel作为数据源导入时 错误 0xc002f210: 准备 SQL 任务: 执行查询“”失败
- 一个弱弱的查询问题
- oracle表数据的自增长
- odbc无法取oracle中timestamp类型?
- 高分:一树形结构的sql查询问题
- pl/sql连接oracle 10g不上
- 关于JDBC的一个扑捉异常的问题
- select in 限制问题 有哪个知道? 高手请看一眼.帮忙!!
- 关于一个数据库访问的问题。。。。。。急。。。。。。
- 小弟学习Oracle时开发的一个小软件Oracle Assistant,各位能帮我试用测试的请留下Email
- merge into 新疑问
- oracle定时日期
row_number()(partition by order by )
ID NAME
--- ----------
1 aaaa
1 bbbb
1 cccc
2 dddd
2 eeee
2 ffff
3 gggg
4 gggg
4 gggg
5 gggg
5 gggg
5 gggg
12 rows selected
SQL>
SQL> SELECT ID,NAME FROM (
2 SELECT ID ,NAME,row_number()over(PARTITION BY a.ID ORDER BY a.name ASC)rn FROM (SELECT DISTINCT * FROM test) a )
3 WHERE rn<=2;
ID NAME
--- ----------
1 aaaa
1 bbbb
2 dddd
2 eeee
3 gggg
4 gggg
5 gggg
7 rows selected
SQL>
(
select id,name,row_number()over(partition by id order by rowid) as row_
from table_name
) where row_ <= 2
(
select 1 id,'aaaa' cha from dual
union all
select 1 id,'bbbb' cha from dual
)
select id, max(cha) from b group by id
SQL> select * from test;
ID NAME
--- ----------
1 aaaa
1 bbbb
1 cccc
2 dddd
2 eeee
2 ffff
3 gggg
4 gggg
4 gggg
5 gggg
5 gggg
5 gggg
12 rows selected
SQL>
SQL> SELECT ID,NAME FROM (
2 SELECT ID ,NAME,row_number()over(PARTITION BY a.ID ORDER BY a.name ASC)rn FROM test a )
3 WHERE rn<=2;
ID NAME
--- ----------
1 aaaa
1 bbbb
2 dddd
2 eeee
3 gggg
4 gggg
4 gggg
5 gggg
5 gggg
9 rows selected
SQL>
from (select id,name,row_number() over(partition by id order by name,rowid) rn from tb) a where a.rn<=2