Oracle案例陈述
问题描述:
请考虑下面的这个表格并给出结果集。现在,只要满足其中一个条件,我如何才能获得一条记录。Oracle案例陈述
SQL> SELECT ename
2 , job
3 , CASE
4 WHEN sal < 1000
5 THEN 'Low paid'
6 WHEN sal BETWEEN 1001 AND 2000
7 THEN 'Reasonably well paid'
8 WHEN sal BETWEEN 2001 AND 3001
9 THEN 'Well paid'
10 ELSE 'Overpaid'
11 END AS pay_status
12 FROM emp;
ENAME JOB PAY_STATUS
---------- --------- --------------------
SMITH CLERK Low paid
ALLEN SALESMAN Reasonably well paid
WARD SALESMAN Reasonably well paid
JONES MANAGER Well paid
MARTIN SALESMAN Reasonably well paid
BLAKE MANAGER Well paid
CLARK MANAGER Well paid
SCOTT ANALYST Well paid
KING PRESIDENT Overpaid
TURNER SALESMAN Reasonably well paid
ADAMS CLERK Reasonably well paid
JAMES CLERK Low paid
FORD ANALYST Well paid
MILLER CLERK Reasonably well paid
答
为了得到只有一个记录满足每一个条件,在SELECT
声明适用DISTICT
。
SQL> SELECT DISTINCT ename 2 , job 3 , CASE 4 WHEN sal < 1000 5 THEN 'Low paid' 6 WHEN sal BETWEEN 1001 AND 2000 7 THEN 'Reasonably well paid' 8 WHEN sal BETWEEN 2001 AND 3001 9 THEN 'Well paid' 10 ELSE 'Overpaid' 11 END AS pay_status 12 FROM emp;
您可以用'rownum'功能 – Aramillo 2014-09-26 18:28:54
可能的工作,但如果第一个条件不满足什么(IE)没有记录存在这个condn,第二个条件通行证和应只返回第二行等等。 – Remo 2014-09-26 18:32:26
'ROWNUM'会从整个结果集中随机返回有限的记录。但是,OP要为每个条件设置“DISTINCT”行。 – 2014-09-26 18:35:14