SELECT '' AS deptCD, '000' AS deptName FROM DUAL UNION SELECT DEPT_CD AS deptCD, DEPT_NM AS deptName FROM THO01M WHERE abl_dt=to_date('9999-12-31','yyyy-mm-dd') AND use_flag = 'Y' AND SECT_CD = 'SDF00'
不知道这样行不行 我这没ORACLE环境 没试 SELECT * FROM ( SELECT '' AS deptCD, '000' AS deptName FROM DUAL UNION SELECT DEPT_CD AS deptCD, DEPT_NM AS deptName FROM THO01M WHERE abl_dt=to_date('9999-12-31','yyyy-mm-dd') AND use_flag = 'Y' AND SECT_CD = 'SDF00' ) ORDER BY deptCD
举个例子说吧,10和2,如果字段是varchar2型,那么排序的结果是10在前,2在后。如果是number型,那么排序是2在前,10在后。因此上面的sql语句应该写成 SELECT * FROM (SELECT '' AS deptCD, '000' AS deptName FROM DUAL UNION SELECT DEPT_CD AS deptCD, DEPT_NM AS deptName FROM THO01M WHERE abl_dt=to_date('9999-12-31','yyyy-mm-dd') AND use_flag = 'Y' AND SECT_CD = 'SDF00') ORDER BY TO_NUMBER(deptName);
'000' AS deptName
FROM DUAL
UNION SELECT DEPT_CD AS deptCD,
DEPT_NM AS deptName
FROM THO01M
WHERE abl_dt=to_date('9999-12-31','yyyy-mm-dd')
AND use_flag = 'Y'
AND SECT_CD = 'SDF00'
SELECT * FROM (
SELECT '' AS deptCD,
'000' AS deptName
FROM DUAL
UNION SELECT DEPT_CD AS deptCD,
DEPT_NM AS deptName
FROM THO01M
WHERE abl_dt=to_date('9999-12-31','yyyy-mm-dd')
AND use_flag = 'Y'
AND SECT_CD = 'SDF00'
) ORDER BY deptCD
SELECT * FROM
(SELECT '' AS deptCD,
'000' AS deptName
FROM DUAL
UNION SELECT DEPT_CD AS deptCD,
DEPT_NM AS deptName
FROM THO01M
WHERE abl_dt=to_date('9999-12-31','yyyy-mm-dd')
AND use_flag = 'Y'
AND SECT_CD = 'SDF00')
ORDER BY TO_NUMBER(deptName);
对这个新的列来排序。
可以保证肯定正确的。