oracle 中SELECT b.EMPLOYEE_CODE as me
FROM VB_TEST_EMPLOYEE b
order by me
这样是可行的,为什么,下面不行啊,怎么解决
SELECT
CASE WHEN b.EMPLOYEE_CODE ='12' THEN
b.EMPLOYEE_NAME
ELSE
b.EMPLOYEE_NAME
END AS NAME
FROM VB_TEST_EMPLOYEE b
where NAME LIKE 'd%'
FROM VB_TEST_EMPLOYEE b
order by me
这样是可行的,为什么,下面不行啊,怎么解决
SELECT
CASE WHEN b.EMPLOYEE_CODE ='12' THEN
b.EMPLOYEE_NAME
ELSE
b.EMPLOYEE_NAME
END AS NAME
FROM VB_TEST_EMPLOYEE b
where NAME LIKE 'd%'
SELECT b.EMPLOYEE_CODE as me
FROM VB_TEST_EMPLOYEE b
order by me;
也可以改成
SELECT b.EMPLOYEE_CODE as me
FROM VB_TEST_EMPLOYEE b
order by 1;为什么哪?原因是oracle从数据文件中取出数据后,排序操作是不需必须使用原始列名的。而where条件中的列名必须是from的对象中的列名,不能是alias的名子,因为取数时是要靠实际列名的数据来筛选的。如果想用你给的alias列名,可以再嵌套一次:
select * from(SELECT CASE
WHEN B.EMPLOYEE_CODE = '12' THEN
B.EMPLOYEE_NAME
ELSE
B.EMPLOYEE_NAME
END AS NAME
FROM VB_TEST_EMPLOYEE B)
WHERE NAME LIKE 'd%';
select * from tt
t01 t02
a 1
a 2
b 1
b 3------------------------------------------------------------
select case when t01='a' then t02 else t02 end as new from tt
where t01 LIKE 'a%' order by 1result:
new
1
2
第二个sql是不可以的.因为where后面需要真实列名.不可以用
别名.