有如下表结构:
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
今天一兄弟问我,愣了
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