SQL(Oracle)在where语句中使用两个定义的列表

Question

我正在尝试创建一个查询，其中我使用两个具有多个值的预定义列表作为质押位置中的过滤器。这样我可以轻松地对许多值对 en 许多值对重用查询。

在Stackoverflow上，我发现：

WITH MyListOfValues(col1) AS (
select 'MyValue1' from dual union
select 'MyValue2' from dual union
select 'MyValue3' from dual 
)
SELECT * 
FROM DatabaseTable  
WHERE Column in (
select col1
from  MyListOfValues);

Howerver 当我执行以下操作时它会失败：

WITH MyListOfValues1(col1) AS (
select 'MyValue1' from dual union
select 'MyValue2' from dual union
select 'MyValue3' from dual 
)
AND
WITH MyListOfValues2(col1) AS (
select 'MyValue1' from dual union
select 'MyValue2' from dual union
select 'MyValue3' from dual 
)
SELECT * 
FROM DatabaseTable  
WHERE Column1 in (
select col1
from  MyListOfValues1)
AND Column2 in (
select col1
from  MyListOfValues2);

有人有解决方案吗:)我在一个选址的公司环境中这样做，我没有权利(到目前为止我知道(创建自己的表格。

希望你能帮助我！所有的帮助将不胜感激：-(

Answer 1

您有语法错误。 CTE 的正确语法是：

WITH MyListOfValues1(col1) AS (
select 'MyValue1' from dual union
select 'MyValue2' from dual union
select 'MyValue3' from dual 
),
MyListOfValues2(col1) AS (
select 'MyValue1' from dual union
select 'MyValue2' from dual union
select 'MyValue3' from dual 
)
SELECT * 
FROM DatabaseTable  
WHERE Column1 in (select col1 from  MyListOfValues1) AND
Column2 in (select col1 from  MyListOfValues2);

Answer 2

我会改用exits

WITH MyListOfValues1(col1) AS (
select 'MyValue1' from dual union
select 'MyValue2' from dual union
select 'MyValue3' from dual 
),
MyListOfValues2(col1) AS (
select 'MyValue1' from dual union
select 'MyValue2' from dual union
select 'MyValue3' from dual 
)
SELECT * 
FROM DatabaseTable dt  
WHERE EXISTS (SELECT 1 FROM MyListOfValues1 WHERE col1 = dt.Column1) AND
EXISTS (SELECT 1 FROM MyListOfValues2 WHERE col1 = dt.Column2);

Answer 3

使用集合和MEMBER OF运算符

SYS.ODCIVARCHAR2LIST是内置VARRAY您可以使用：

SELECT * 
FROM DatabaseTable  
WHERE Column1 MEMBER OF SYS.ODCIVARCHAR2LIST( 'MyValue1', 'MyValue2', 'MyValue3' )
AND   Column2 MEMBER OF SYS.ODCIVARCHAR2LIST( 'MyValue1', 'MyValue2', 'MyValue3' );

或者，您可以定义自己的集合：

CREATE TYPE StringList IS TABLE OF VARCHAR2(20);
SELECT * 
FROM DatabaseTable  
WHERE Column1 MEMBER OF StringList( 'MyValue1', 'MyValue2', 'MyValue3' )
AND   Column2 MEMBER OF StringList( 'MyValue1', 'MyValue2', 'MyValue3' );

您甚至可以将集合作为绑定变量传递：

SELECT * 
FROM DatabaseTable  
WHERE Column1 MEMBER OF :List1
AND   Column2 MEMBER OF :List2;