Oracle条件条件where子句 [英] Oracle Conditional where clause
问题描述
有什么方法可以编写具有以下功能的查询,添加where子句作为条件方式,
is there any way to write query with following functionality, add where clause as a conditional way,
select e.emp_id, emp.admin_user from employees e
如果emp.admin!='Y'
if emp.admin != 'Y'
然后
查询使用where子句
query run with where clause
其他
查询运行时没有where子句?
query run without where clause ?
推荐答案
在 WHERE 子句中使用 CASE 表达式应该可以解决问题.如果您说如果不满足条件就不需要where子句,那么您所需要的只是像WHERE 1 = 1
这样的条件,即,当不满足条件时返回所有行.因此,您需要像往常一样将不满足条件设为 TRUE .
Using a CASE expression in the WHERE clause should do the trick. When you say you don't need the where clause if condition is not met, then all you want is a condition like WHERE 1 = 1
, i.e. when condition is not met then return all rows. So, you need to make the not met condition as always TRUE.
例如,
我有一个员工表,
SQL> SELECT empno, ename, deptno
2 FROM emp;
EMPNO ENAME DEPTNO
---------- ---------- ----------
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7566 JONES 20
7654 MARTIN 30
7698 BLAKE 30
7782 CLARK 10
7788 SCOTT 20
7839 KING 10
7844 TURNER 30
7876 ADAMS 20
7900 JAMES 30
7902 FORD 20
7934 MILLER 10
14 rows selected.
SQL>
我想选择员工详细信息,如果部门为20,则使用where子句,否则返回所有员工详细信息,但过滤满足where条件的部门.
I want to select the employee details, if department is 20 then use the where clause else return all the employee details, but filter the department which meets the where condition.
SQL> SELECT empno, ename, deptno
2 FROM emp
3 WHERE ename =
4 CASE
5 WHEN deptno = 20
6 THEN 'SCOTT'
7 ELSE ename
8 END
9 /
EMPNO ENAME DEPTNO
---------- ---------- ----------
7499 ALLEN 30
7521 WARD 30
7654 MARTIN 30
7698 BLAKE 30
7782 CLARK 10
7788 SCOTT 20
7839 KING 10
7844 TURNER 30
7900 JAMES 30
7934 MILLER 10
10 rows selected.
SQL>
因此,对于部门20,该过滤器由where子句应用,而我仅获得ename SCOTT的行,对于其他人,它返回所有行.
So, for department 20, the filter is applied by where clause, and I get only the row for ename SCOTT, for others it returns all the rows.
这篇关于Oracle条件条件where子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!