Oracle查询以匹配表中所有行中列表中的所有值 [英] Oracle query to match all values in the list among all rows in table
问题描述
我的表格结构如下:
ID A B
-- --- -----
1 a a1
2 b b1
3 a a2
4 c a1
5 b b2
我想要A列中的这些值，这些值与列表中B的所有值相关.
例如:
我有一个A的列表:
{a1,a2}
输出应为a，
在结果中返回c是NOT，因为它仅与a1相关，与a2无关.
是否可以通过SQL查询获得此结果?
已编辑
它应该在这种特殊情况下起作用:
ID A B
-- --- -----
1 a a1
2 b b1
3 a a2
4 c a1
5 b b2
6 c a3
7 c a2
现在c也与a2和a3相关，但不应返回它，因为作为结果的一部分，c应该与a1和a2完全相关
但是如果我查询如下:
SELECT A
FROM table_name
WHERE B IN ( 'a1', 'a2' ) -- The items in the list
GROUP BY A
HAVING COUNT( DISTINCT b ) = 2;
它也返回c.
Oracle安装程序:
CREATE TABLE table_name ( ID, A, B ) AS
SELECT 1, 'a', 'a1' FROM DUAL UNION ALL
SELECT 2, 'b', 'b1' FROM DUAL UNION ALL
SELECT 3, 'a', 'a2' FROM DUAL UNION ALL
SELECT 4, 'c', 'a1' FROM DUAL UNION ALL
SELECT 5, 'b', 'b2' FROM DUAL;
查询-使用GROUP BY和COUNT( DISTINCT ... ) :
SELECT A
FROM table_name
WHERE B IN ( 'a1', 'a2' ) -- The items in the list
GROUP BY A
HAVING COUNT( DISTINCT b ) = 2; -- The number of items in the list
输出:
A
-
a
查询-动态传递列表:
CREATE OR REPLACE TYPE stringlist IS TABLE OF VARCHAR2(10);
/
SELECT A
FROM table_name
WHERE B MEMBER OF :your_list
GROUP BY A
HAVING COUNT( DISTINCT B ) = CARDINALITY( :your_list );
其中绑定变量:your_list的类型为stringlist.
如果列表以定界字符串传递，则可以使用 PL/SQL函数，该函数会将其作为可以插入上述查询的集合返回. >
更新:
SELECT A
FROM table_name
GROUP BY A
HAVING COUNT( DISTINCT CASE WHEN b IN ( 'a1', 'a2' ) THEN b END ) = 2
AND COUNT( DISTINCT CASE WHEN b NOT IN ( 'a1', 'a2' ) THEN b END ) = 0;
或
SELECT A
FROM table_name
GROUP BY A
HAVING COUNT( DISTINCT CASE WHEN b MEMBER OF :your_list THEN b END ) = CARDINALITY( :your_list )
AND COUNT( DISTINCT CASE WHEN b NOT MEMBER OF :your_list THEN b END ) = 0;
I have a table structure as under:
ID A B
-- --- -----
1 a a1
2 b b1
3 a a2
4 c a1
5 b b2
I want such values from column A, who are related to ALL the values of B in a list.
For Example:
I have a list of A's:
{a1,a2}
Output should be a,
c is NOT returned in the result because it is related to a1 only and not a2.
Is there a way to get this result by a SQL query?
Edited
It should work in this special case:
ID A B
-- --- -----
1 a a1
2 b b1
3 a a2
4 c a1
5 b b2
6 c a3
7 c a2
Now c is also related to a2 and a3, but it should NOT be returned because for being a part of result, c should be related to exactly a1 AND a2
But if I query as follows:
SELECT A
FROM table_name
WHERE B IN ( 'a1', 'a2' ) -- The items in the list
GROUP BY A
HAVING COUNT( DISTINCT b ) = 2;
It returns c too.
Oracle Setup:
CREATE TABLE table_name ( ID, A, B ) AS
SELECT 1, 'a', 'a1' FROM DUAL UNION ALL
SELECT 2, 'b', 'b1' FROM DUAL UNION ALL
SELECT 3, 'a', 'a2' FROM DUAL UNION ALL
SELECT 4, 'c', 'a1' FROM DUAL UNION ALL
SELECT 5, 'b', 'b2' FROM DUAL;
Query - Use GROUP BY and COUNT( DISTINCT ... ):
SELECT A
FROM table_name
WHERE B IN ( 'a1', 'a2' ) -- The items in the list
GROUP BY A
HAVING COUNT( DISTINCT b ) = 2; -- The number of items in the list
Output:
A
-
a
Query - Passing the list dynamically:
CREATE OR REPLACE TYPE stringlist IS TABLE OF VARCHAR2(10);
/
SELECT A
FROM table_name
WHERE B MEMBER OF :your_list
GROUP BY A
HAVING COUNT( DISTINCT B ) = CARDINALITY( :your_list );
Where the bind variable :your_list is of type stringlist.
If the list is passed as a delimited string then you can use any of the techniques in the Splitting delimited strings documentation page to separate it. There is a simple PL/SQL function that would return it as a collection that could be plugged into the above query.
Update:
SELECT A
FROM table_name
GROUP BY A
HAVING COUNT( DISTINCT CASE WHEN b IN ( 'a1', 'a2' ) THEN b END ) = 2
AND COUNT( DISTINCT CASE WHEN b NOT IN ( 'a1', 'a2' ) THEN b END ) = 0;
or
SELECT A
FROM table_name
GROUP BY A
HAVING COUNT( DISTINCT CASE WHEN b MEMBER OF :your_list THEN b END ) = CARDINALITY( :your_list )
AND COUNT( DISTINCT CASE WHEN b NOT MEMBER OF :your_list THEN b END ) = 0;
这篇关于Oracle查询以匹配表中所有行中列表中的所有值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
