用scott用户登录oracle,根据emp表查询员工资信息
其中工资等级标准如下:
GRADE(等级) LOSAL(下限) HISAL(上线)
1, 700, 1200
2, 1201, 1400
3, 1401, 2000
4, 2001, 3000
5, 3001, 9999请问如何用一个case语句查询?
我的查询语句是:
SELECT EMPNO,ENAME,JOB,MGR,
HIREDATE,DEPTNO,SAL,
CASE
WHEN SAL BETWEEN 700 AND 1200 THEN 1;
WHEN SAL BETWEEN 1201 AND 1400 THEN 2;
WHEN SAL BETWEEN 1401 AND 2000 THEN 3;
WHEN SAL BETWEEN 2001 AND 3000 THEN 4;
WHEN SAL BETWEEN 3001 AND 9999 THEN 5;
END CASE AS GRADE,COMM
FROM EMP;但是在运行时报错,ORA-00905:缺失关键字。哪位仁义的大侠能够相助,感激不尽……
一定要能够运行的代码。并且告诉我,我这个代码错在什么地方。
其中工资等级标准如下:
GRADE(等级) LOSAL(下限) HISAL(上线)
1, 700, 1200
2, 1201, 1400
3, 1401, 2000
4, 2001, 3000
5, 3001, 9999请问如何用一个case语句查询?
我的查询语句是:
SELECT EMPNO,ENAME,JOB,MGR,
HIREDATE,DEPTNO,SAL,
CASE
WHEN SAL BETWEEN 700 AND 1200 THEN 1;
WHEN SAL BETWEEN 1201 AND 1400 THEN 2;
WHEN SAL BETWEEN 1401 AND 2000 THEN 3;
WHEN SAL BETWEEN 2001 AND 3000 THEN 4;
WHEN SAL BETWEEN 3001 AND 9999 THEN 5;
END CASE AS GRADE,COMM
FROM EMP;但是在运行时报错,ORA-00905:缺失关键字。哪位仁义的大侠能够相助,感激不尽……
一定要能够运行的代码。并且告诉我,我这个代码错在什么地方。
HIREDATE,DEPTNO,SAL,
CASE
WHEN SAL BETWEEN 700 AND 1200 THEN 1
WHEN SAL BETWEEN 1201 AND 1400 THEN 2
WHEN SAL BETWEEN 1401 AND 2000 THEN 3
WHEN SAL BETWEEN 2001 AND 3000 THEN 4
WHEN SAL BETWEEN 3001 AND 9999 THEN 5
END AS GRADE,COMM
FROM EMP;
end末尾多了不必要的case
至于你原先的语句为什么错 shiyiwan的已经讲了
SQL> SELECT EMPNO,ENAME,JOB,MGR,
2 HIREDATE,DEPTNO,SAL,
3 CASE
4 WHEN SAL BETWEEN 700 AND 1200 THEN 1
5 WHEN SAL BETWEEN 1201 AND 1400 THEN 2
6 WHEN SAL BETWEEN 1401 AND 2000 THEN 3
7 WHEN SAL BETWEEN 2001 AND 3000 THEN 4
8 WHEN SAL BETWEEN 3001 AND 9999 THEN 5
9 END AS GRADE,COMM
10 FROM EMP;
EMPNO ENAME JOB MGR HIREDATE DEPTNO SAL GRADE COMM
----- ---------- --------- ----- ---------- ------ ----- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 20 800 1
7499 ALLEN SALESMAN 7698 20-FEB-81 30 1600 3 300
7521 WARD SALESMAN 7698 22-FEB-81 30 1250 2 500
7566 JONES MANAGER 7839 02-APR-81 20 2975 4
7654 MARTIN SALESMAN 7698 28-SEP-81 30 1250 2 1400
7698 BLAKE MANAGER 7839 01-MAY-81 30 2850 4
7782 CLARK MANAGER 7839 09-JUN-81 10 2450 4
7788 SCOTT ANALYST 7566 19-APR-87 20 3000 4
7839 KING PRESIDENT 17-NOV-81 10 5000 5
7844 TURNER SALESMAN 7698 08-SEP-81 30 1500 3 0
7876 ADAMS CLERK 7788 23-MAY-87 20 1100 1EMPNO ENAME JOB MGR HIREDATE DEPTNO SAL GRADE COMM
----- ---------- --------- ----- ---------- ------ ----- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 30 950 1
7902 FORD ANALYST 7566 03-DEC-81 20 3000 4
7934 MILLER CLERK 7782 23-JAN-82 10 1300 214 rows selected.