求助一条SQL,排序问题表结构:
create table student
(
ID number,
name varchar2(20),
sex varchar2(2)
)表数据:
insert into student (id,name,sex)values(1,'dd','男');
insert into student (id,name,sex)values(2,'aa','女');
insert into student (id,name,sex)values(3,'bb','女');
insert into student (id,name,sex)values(4,'cc','男');
insert into student (id,name,sex)values(5,'bb','女');
insert into student (id,name,sex)values(6,'ee','女');
insert into student (id,name,sex)values(7,'aa','女');
insert into student (id,name,sex)values(8,'ee','女');希望得到的排序结果: ID NAME SEX
---------- -------------------- ---
1 dd 男
2 aa 女
7 aa 女
3 bb 女
5 bb 女
4 cc 男
6 ee 女
8 ee 女把 name 相同的放一起,但是 id 按从小到大排。请大虾指点。
create table student
(
ID number,
name varchar2(20),
sex varchar2(2)
)表数据:
insert into student (id,name,sex)values(1,'dd','男');
insert into student (id,name,sex)values(2,'aa','女');
insert into student (id,name,sex)values(3,'bb','女');
insert into student (id,name,sex)values(4,'cc','男');
insert into student (id,name,sex)values(5,'bb','女');
insert into student (id,name,sex)values(6,'ee','女');
insert into student (id,name,sex)values(7,'aa','女');
insert into student (id,name,sex)values(8,'ee','女');希望得到的排序结果: ID NAME SEX
---------- -------------------- ---
1 dd 男
2 aa 女
7 aa 女
3 bb 女
5 bb 女
4 cc 男
6 ee 女
8 ee 女把 name 相同的放一起,但是 id 按从小到大排。请大虾指点。
order by name,id
你这个应该是优先按name排序,name相同再按id排序吧。不过我不理解为什么dd会排在第一个
就只这里难解决,我希望 id=1 排第一个;先按姓名分组,再按组里面 id 最小的排序
order by decode(id,1,1),name,id
这样的话select * from student order by
decode(name,(select name from student where id=1),1)
,name,id
select id,
name,
sex
from student
order by min(id) over(partition by name), id
name,
sex
from student
order by min(id) over(partition by name)这样不就好了?
你的那样 如果2个组min(id)一样的 不是就混乱了
name,
sex
from student
order by min(id) over(partition by name), id
学习
用这个结果的话,实际上是按id排序,和name没有关系。如果id为2的不是a,而是f呢
岂不是和你在2楼的描述冲突?
name,
sex
from student
order by min(id) over(partition by name)所以说要把最后的id去掉