表A 中有列 code 内容为:3301-1
3301-22
3301-3
3301-2
3301-13
3301-11
3301-12如何按下面的顺序排序
3301-1
3301-2
3301-3
3301-11
3301-12
3301-13
3301-22我用的版本 oracle 9i
3301-22
3301-3
3301-2
3301-13
3301-11
3301-12如何按下面的顺序排序
3301-1
3301-2
3301-3
3301-11
3301-12
3301-13
3301-22我用的版本 oracle 9i
解决方案 »
- PLSQL Developer导出数据有乱码如何解决?
- Oracle 如何这样分区?
- 数据行转列的问题
- SQL查询not in,或是同一字段多个!=效率超低,怎样优化??
- 关于每人次读取不同数据并操作的问题。(实在不知道该起个什么名字好)
- Oracle用户请注意了,好消息要与大家一同分享!千万不要错过!
- 来了就有分,很急,救命
- 请教:ORA-01009: missing mandatory parameter
- 我写了一个存储过程效率很低,请问有什么办法能提高效率
- oracle同服务器分项目的问题
- 新建的用户想要查询视图,应该赋予哪些权限
- informaker连接oracle数据库时候报错:ora-00942:table or view does not exist
select * from 表A order by to_number(substr(code,6));
with tmp as
(
select '3301-1' code from dual
union
select '3301-22' code from dual
union
select '3301-3' code from dual
union
select '3301-2' code from dual
union
select '3301-13' code from dual
union
select '3301-11' code from dual
union
select '3302-12' code from dual
)
select * from tmp order by to_number(substr(code,1,4)), to_number(substr(code,6));
3301-1 2010
3301-22 2010
3301-3 2010
3301-2 2010
3301-13 2010
3301-11 2010
3301-12 2010
查询2010年的记录 如何按下面的顺序排序
3301-1
3301-2
3301-3
3301-11
3301-12
3301-13
3301-22我用的版本 oracle 9i??
先谢谢上面各位!!
with tmp as
(
select '3301-1' code, '2010' year from dual
union
select '3301-22' code, '2010' year from dual
union
select '3301-3' code, '2010' year from dual
union
select '3301-2' code, '2010' year from dual
union
select '3301-13' code, '2010' year from dual
union
select '3301-11' code, '2010' year from dual
union
select '3302-12' code, '2010' year from dual
union
select '3302-2' code, '2010' year from dual
union
select '3304-12' code, '2009' year from dual
union
select '3304-2' code, '2009' year from dual
)
select * from tmp
where year = '2010'
order by to_number(substr(code,1,instr(code,'-')-1)), to_number(substr(code,instr(code,'-')+1));
如果记录很多,用union不合适吧!
还有to_number函数可以用于where子句中吗??
to_number可以用于where子句中
CREATE TABLE a(code varchar(10));INSERT INTO a(code) values('3301-1');
INSERT INTO a(code) values('3301-22');
INSERT INTO a(code) values('3301-3');
INSERT INTO a(code) values('3301-2');
INSERT INTO a(code) values('3301-13');
INSERT INTO a(code) values('3301-13');
INSERT INTO a(code) values('3301-11');
INSERT INTO a(code) values('3301-12');
SELECT code
FROM a
ORDER BY substr(code,1,length('-')-1) || lpad( substr(code,length('-')+1,length(code)),2,'0');
CREATE TABLE a(code varchar(10));INSERT INTO a(code) values('3301-1');
INSERT INTO a(code) values('3301-22');
INSERT INTO a(code) values('3301-3');
INSERT INTO a(code) values('3301-2');
INSERT INTO a(code) values('3301-13');
INSERT INTO a(code) values('3301-13');
INSERT INTO a(code) values('3301-11');
INSERT INTO a(code) values('3301-12');-- 方法一:
SELECT code
FROM a
ORDER BY substr(code,1,length('-')-1) || lpad( substr(code,length('-')+1,length(code)),2,'0');-- 方法二:
SELECT code
FROM a
ORDER BY substr(code,1,length('-')-1), substr(code,length('-')+1,length(code));