tableA 变换后
num id name num id name
1 001 A 1 j001 A
2 002 B 6 j001 A
3 003 C 7 j001 A
4 004 D 2 k002 B
5 005 E 8 k002 B
6 k001 A 3 j003 C
7 j001 A 9 j003 C
8 k002 B 4 004 D
9 j003 C 10 004 D
10 null D 5 005 E
11 a005 E 11 005 E规则就是:name值相同的看id值的第一位是否是j或k(如果j,k都有,升序取前),如果不为j,k升序取前,null排最后.谢谢!
num id name num id name
1 001 A 1 j001 A
2 002 B 6 j001 A
3 003 C 7 j001 A
4 004 D 2 k002 B
5 005 E 8 k002 B
6 k001 A 3 j003 C
7 j001 A 9 j003 C
8 k002 B 4 004 D
9 j003 C 10 004 D
10 null D 5 005 E
11 a005 E 11 005 E规则就是:name值相同的看id值的第一位是否是j或k(如果j,k都有,升序取前),如果不为j,k升序取前,null排最后.谢谢!
2 from (select 1 as num,'001' as id,'A' as name from dual
3 union all
4 select 2 as num,'002' as id,'B' as name from dual
5 union all
6 select 3 as num,'003' as id,'C' as name from dual
7 union all
8 select 4 as num,'004' as id,'D' as name from dual
9 union all
10 select 5 as num,'005' as id,'E' as name from dual
11 union all
12 select 6 as num,'k001' as id,'A' as name from dual
13 union all
14 select 7 as num,'j001' as id,'A' as name from dual
15 union all
16 select 8 as num,'k002' as id,'B' as name from dual
17 union all
18 select 9 as num,'j003' as id,'C' as name from dual
19 union all
20 select 10 as num,null as id,'D' as name from dual
21 union all
22 select 11 as num,'a005' as id,'E' as name from dual
23 )tt
24 order by tt.name,replace(replace(tt.id,'k','-'),'j','-'); NUM ID NAME
---------- ---- ----
6 k001 A
7 j001 A
1 001 A
8 k002 B
2 002 B
9 j003 C
3 003 C
4 004 D
10 D
5 005 E
11 a005 E11 rows selected
mantisXF(枫の叶)
给你的语句的FROM开始到TT换成你的表名就行了
他习惯用union方法写测试数据的,不知道的人看不懂的
其实就是
select *
from tablea tt
order by tt.name,replace(replace(tt.id,'k','-'),'j','-');
tableA
seq num id name value
1 A001 001 A 22A
2 A001 002 B 22B
3 A003 003 C 22A
4 A004 004 D 22C
5 A005 005 E 22D
6 A006 k001 A 22A
7 A001 j001 A 22A
8 A002 k002 B 22C
9 A007 j003 C 22J
10 A008 null D 22K
11 A009 a005 E 22M
1.name值相同的看id值的第一位是否是j或k,如果是,把所有的该name值的id值变为含有j,k的id值(如不含的情况下,升序排列,取最前的id值进行设置)
2.num,id,value字段值相同,取其中一个.
3.按num,name进行排序变换及更新后
seq num id name value
1 A001 j001 A 22A
2 A001 k002 B 22B
3 A002 k002 B 22C
4 A003 j003 C 22A
5 A004 004 D 22C
6 A005 a005 E 22D
7 A006 j001 A 22A
8 A007 j003 C 22J
9 A008 004 D 22K
10 A009 a005 E 22M
A的ID有(001,k001,j001)->取j001,D的ID为(004,null)->null排最后->004;当把SEQ=1记录中的ID变为j001时,就与SEQ=7的记录相等,因此只取其中一条,其它以次类推,请指教!!
select distinct * from(
select m.num, n.id, m.name, m.value from tableA m, (
select num, id, name from tableA a where
(select count(1) from tableA b where a.name=b.name and nvl(a.id,'000')>nvl(b.id,'000'))=1 ) n
where m.name=n.name) order by num, name)t;可以出来结果,但不知道效率如何.