英文:
swap one female name in-between two male name
问题
尝试将两个男性姓名之间的一个女性姓名进行交换
输入
Id Name Sal Gender
1 AA 2000 M
2 BB 1500 M
3 CC 3000 F
预期输出可能是
Id Name Sal Gender
1 AA 2000 M
3 CC 3000 F
2 BB 1500 M
英文:
I tried to swap one female name in between two male names
Input
Id Name Sal Gender
1 AA 2000 M
2 BB 1500 M
3 CC 3000 F
expected output could be
Id Name Sal Gender
1 AA 2000 M
3 CC 3000 F
2 BB 1500 M
答案1
得分: 1
一个选项是按性别分组，获取每个性别的rownum，以便后续排序返回所需的输出。
使用示例数据：
SQL> select * from test;
ID NAME SAL GENDER
---------- ----- ---------- ----------
1 AA 2000 M
2 BB 1500 M
3 CC 3000 F
查询：
SQL> with temp as(
2 select t.*, rownum rn
3 from test t
4 where gender = 'M'
5 union
6 select t.*, rownum rn
7 from test t
8 where gender = 'F'
9 )
10 select row_number() over (order by rn) id,
11 name, sal, gender
12 from temp
13 order by rn, gender;
ID NAME SAL GENDER
---------- ----- ---------- ----------
1 AA 2000 M
2 CC 3000 F
3 BB 1500 M
SQL>;
英文:
One option is to split people by gender, fetching rownum for each of them so that later sorting returns desired output.
With sample data:
SQL> select * from test;
ID NAME SAL GENDER
---------- ----- ---------- ----------
1 AA 2000 M
2 BB 1500 M
3 CC 3000 F
Query:
SQL> with temp as(
2 select t.*, rownum rn
3 from test t
4 where gender = 'M'
5 union
6 select t.*, rownum rn
7 from test t
8 where gender = 'F'
9 )
10 select row_number() over (order by rn) id,
11 name, sal, gender
12 from temp
13 order by rn, gender;
ID NAME SAL GENDER
---------- ----- ---------- ----------
1 AA 2000 M
2 CC 3000 F
3 BB 1500 M
SQL>
答案2
得分: 0
尝试这个：
SELECT ID, A_NAME, SAL, GENDER
FROM ( Select
ID, A_NAME, SAL, GENDER, ROWNUM "RN", ROW_NUMBER() OVER(Partition By GENDER Order By ID) "GENDER_RN"
From tbl
Order By ID
)
Order By GENDER_RN, RN
... 使用您的示例数据：
WITH
tbl AS
( Select 1 ID, 'AA' "A_NAME", 2000 "SAL", 'M' "GENDER" From Dual Union All
Select 2 ID, 'BB' "A_NAME", 1500 "SAL", 'M' "GENDER" From Dual Union All
Select 3 ID, 'CC' "A_NAME", 3000 "SAL", 'F' "GENDER" From Dual
)
... 结果如下：
ID A_NAME SAL GENDER
---------- ------ ---------- ------
1 AA 2000 M
3 CC 3000 F
2 BB 1500 M
如果您有更多的行，像这样：
WITH
tbl AS
( Select 1 ID, 'AA' "A_NAME", 2000 "SAL", 'M' "GENDER" From Dual Union All
Select 2 ID, 'BB' "A_NAME", 1500 "SAL", 'M' "GENDER" From Dual Union All
Select 3 ID, 'CC' "A_NAME", 3000 "SAL", 'F' "GENDER" From Dual Union All
--
Select 4 ID, 'DD' "A_NAME", 1800 "SAL", 'F' "GENDER" From Dual Union All
Select 5 ID, 'EE' "A_NAME", 2100 "SAL", 'M' "GENDER" From Dual Union All
Select 6 ID, 'FF' "A_NAME", 2700 "SAL", 'F' "GENDER" From Dual )
... 结果如下：
ID A_NAME SAL GENDER
---------- ------ ---------- ------
1 AA 2000 M
3 CC 3000 F
2 BB 1500 M
4 DD 1800 F
5 EE 2100 M
6 FF 2700 F
要将顺序从 M F M F 反转为 F M F M，请在Order By子句中对RN使用DESC...
英文:
Try this:
SELECT ID, A_NAME, SAL, GENDER
FROM ( Select
ID, A_NAME, SAL, GENDER, ROWNUM "RN", ROW_NUMBER() OVER(Partition By GENDER Order By ID) "GENDER_RN"
From tbl
Order By ID
)
Order By GENDER_RN, RN
... with your sample data:
WITH
tbl AS
( Select 1 ID, 'AA' "A_NAME", 2000 "SAL", 'M' "GENDER" From Dual Union All
Select 2 ID, 'BB' "A_NAME", 1500 "SAL", 'M' "GENDER" From Dual Union All
Select 3 ID, 'CC' "A_NAME", 3000 "SAL", 'F' "GENDER" From Dual
)
... it results with:
ID A_NAME SAL GENDER
---------- ------ ---------- ------
1 AA 2000 M
3 CC 3000 F
2 BB 1500 M
And if you have some more rows like here:
WITH
tbl AS
( Select 1 ID, 'AA' "A_NAME", 2000 "SAL", 'M' "GENDER" From Dual Union All
Select 2 ID, 'BB' "A_NAME", 1500 "SAL", 'M' "GENDER" From Dual Union All
Select 3 ID, 'CC' "A_NAME", 3000 "SAL", 'F' "GENDER" From Dual Union All
--
Select 4 ID, 'DD' "A_NAME", 1800 "SAL", 'F' "GENDER" From Dual Union All
Select 5 ID, 'EE' "A_NAME", 2100 "SAL", 'M' "GENDER" From Dual Union All
Select 6 ID, 'FF' "A_NAME", 2700 "SAL", 'F' "GENDER" From Dual )
... the result is:
ID A_NAME SAL GENDER
---------- ------ ---------- ------
1 AA 2000 M
3 CC 3000 F
2 BB 1500 M
4 DD 1800 F
5 EE 2100 M
6 FF 2700 F
To reverse the order from M F M F into F M F M use DESC on RN in Order By clause...
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论