Oracle 条件 where 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29027425/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Conditional where clause
提问by user3374229
is there any way to write query with following functionality, add where clause as a conditional way,
有什么方法可以编写具有以下功能的查询，添加 where 子句作为条件方式，
select e.emp_id, emp.admin_user from employees e
if emp.admin != 'Y'
then
query run with where clause
else
query run without where clause ?
如果 emp.admin != 'Y'
然后
使用 where 子句运行查询
别的
查询在没有 where 子句的情况下运行？
回答by Lalit Kumar B
Using a CASEexpression in the WHEREclause 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.
在WHERE子句中使用CASE表达式应该可以解决问题。当您说如果条件不满足则不需要 where 子句时，您只需要一个条件，即当条件不满足时返回所有行。因此，您需要始终将未满足的条件设为TRUE。WHERE 1 = 1
For example,
例如，
I have an employee table,
我有一张员工表，
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>
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.
我想选择员工详细信息，如果部门是 20 则使用 where 子句 else 返回所有员工详细信息，但过滤满足 where 条件的部门。
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>
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.
因此，对于部门 20，过滤器由 where 子句应用，我只得到 ename SCOTT 的行，对于其他部门，它返回所有行。
回答by Martina
To keep it simple I would go for union clause in this case, so you can have your where clause as complex as you need. I tried to guess your table structure from above comment, let's see this example:
为了简单起见，在这种情况下我会使用 union 子句，这样您就可以根据需要设置复杂的 where 子句。我试图从上面的评论中猜测你的表结构，让我们看看这个例子：
SQL> create table employees (emp_id number, admin_user number, project_id number);
Table created.
表已创建。
SQL> create table project_accessible_to_user (emp_id number, project_id number);
Table created.
表已创建。
Now make simple union all of two queries one with where condition anoother without it
现在简单地联合所有两个查询，一个有 where 条件，另一个没有它
SQL> select * from employees e where e.admin_user!='Y' and project_id in
(select project_id from project_accessible_to_user where emp_id=e.emp_id)
union all
select * from employees e where (e.admin_user is null or
e.admin_user='Y');
UNION ALL is better from performance point of view as UNION because it means that it is not checking for intersect values so if there are any it will return duplicates. However in this case it is filtered already by condition on admin_user, so these duplicates will not occure.
从性能的角度来看，UNION ALL 比 UNION 更好，因为这意味着它不检查相交值，因此如果有任何相交值，它将返回重复项。然而，在这种情况下，它已经通过 admin_user 的条件过滤，因此不会发生这些重复。
