改一下,字段错了:
(select * from T1 where name in('n1','n4','n3'))
union select * from T1 where name not in ('n1','n4','n3') order by value desc;
(select * from T1 where name in('n1','n4','n3'))
union select * from T1 where name not in ('n1','n4','n3') order by value desc;
后面加个括号试过么?如
(select * from T1 where name in('n1','n4','n3'))
union (select * from T1 where name not in ('n1','n4','n3') order by value desc);
发现用了一个视图就行了,如下:================================================
SQL> select * from test where id in (5,15,23)
2 union
3 (select * from test where id not in(5,15,23) order by name)
4 ;select * from test where id in (5,15,23)
union
(select * from test where id not in(5,15,23) order by name)ORA-00907: missing right parenthesisSQL>
SQL> select * from test where id in (5,15,23)
2 union
3 (select * from (select * from test where id not in(5,15,23) order by name))
4 ; ID NAME
---------- ------------------------------
1 test
2 test
3 test
4 test
5 test
15 test
23 test
25 test
33 test
35 test
45 test
60 test
70 test
80 test
90 test
100 test
101 test
200 test
202 test
1000 test20 rows selectedSQL>
select name ,value ,decode(name,'V1','a00','V4','a01','V3','a02','b'|value) ordV
from table_name
order by ordV 意思大概你看的明白吧。
后面的'b'|value什么意思
使用union合并后还是按照原来的顺序,表中的顺序就是显示的顺序,好像不能改变,怎么办。
select * form t1 where name='n1'
union
select * form t1 where name='n2';
和
select * form t1 where name='n2'
union
select * form t1 where name='n1';
结果是一样的
名称 空? 类型
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(20)
VALUE NUMBERSQL> select * from test ;NAME VALUE
-------------------- ----------
n1 1
n2 2
n3 0
n4 5
n6 8
n7 3已选择6行。SQL> ed
已写入文件 afiedt.buf 1 select name ,value ,decode(name,'n1','a00','n4','a01','n3','a02','b'||value) ordV
2 from Test
3* order by ordV
SQL> /NAME VALUE ORDV
-------------------- ---------- -----------------------------------------
n1 1 a00
n4 5 a01
n3 0 a02
n2 2 b2
n7 3 b3
n6 8 b8已选择6行。SQL>
// 结束不好意思, 前面少写了个 "|" 。 我已经测试通过了。不用什么Union 都可以了。 这个方法最好理解了。
--
1
2
3
4
5
6
67 rows selected以下是以id in (1,2,3)顺序首先排列,然再按not in (1,2,3)降序排序
SQL> select id from
2 (select id,max(id) over() max_id from aa)
3 order by decode(id,'3',max_id+1,'2',max_id+2,'1',max_id+3,id) desc;ID
--
1
2
3
6
6
5
47 rows selected改写楼主如何语句:
select name,value from
(select name,value,max(value) over() max_value from T1)
order by decode(name,'n1',max_value+3,'n4',max_id+2,'n3',max_value+1,value) desc;