请教SQL排序!!
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排最后.谢谢!
[解决办法]
XD,不知道是不是这个意思 ... 你可以试试看~~~
SQL> select *
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 E
11 rows selected
[解决办法]
select rownum seq,t.* from(
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;
可以出来结果,但不知道效率如何.
