但是如果我查询如下:SELECT AFROM table_nameWHERE B IN ( 'a1', 'a2' ) -- The items in the listGROUP BY AHAVING COUNT( DISTINCT b ) = 2;它也返回c. 解决方案 Oracle安装程序:CREATE TABLE table_name ( ID, A, B ) ASSELECT 1, 'a', 'a1' FROM DUAL UNION ALLSELECT 2, 'b', 'b1' FROM DUAL UNION ALLSELECT 3, 'a', 'a2' FROM DUAL UNION ALLSELECT 4, 'c', 'a1' FROM DUAL UNION ALLSELECT 5, 'b', 'b2' FROM DUAL; 查询-使用GROUP BY和COUNT( DISTINCT ... ) :SELECT AFROM table_nameWHERE B IN ( 'a1', 'a2' ) -- The items in the listGROUP BY AHAVING COUNT( DISTINCT b ) = 2; -- The number of items in the list 输出:A-a 查询-动态传递列表:CREATE OR REPLACE TYPE stringlist IS TABLE OF VARCHAR2(10);/SELECT AFROM table_nameWHERE B MEMBER OF :your_listGROUP BY AHAVING COUNT( DISTINCT B ) = CARDINALITY( :your_list ); 其中绑定变量:your_list的类型为stringlist.如果列表以定界字符串传递,则可以使用分割定界字符串文档页面以将其分开.有一个简单的 PL/SQL函数,该函数会将其作为可以插入上述查询的集合返回. > 更新:SELECT AFROM table_nameGROUP BY AHAVING COUNT( DISTINCT CASE WHEN b IN ( 'a1', 'a2' ) THEN b END ) = 2AND COUNT( DISTINCT CASE WHEN b NOT IN ( 'a1', 'a2' ) THEN b END ) = 0;或SELECT AFROM table_nameGROUP BY AHAVING 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 b2I 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?EditedIt 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 a2Now 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 a2But if I query as follows:SELECT AFROM table_nameWHERE B IN ( 'a1', 'a2' ) -- The items in the listGROUP BY AHAVING COUNT( DISTINCT b ) = 2;It returns c too. 解决方案 Oracle Setup:CREATE TABLE table_name ( ID, A, B ) ASSELECT 1, 'a', 'a1' FROM DUAL UNION ALLSELECT 2, 'b', 'b1' FROM DUAL UNION ALLSELECT 3, 'a', 'a2' FROM DUAL UNION ALLSELECT 4, 'c', 'a1' FROM DUAL UNION ALLSELECT 5, 'b', 'b2' FROM DUAL;Query - Use GROUP BY and COUNT( DISTINCT ... ):SELECT AFROM table_nameWHERE B IN ( 'a1', 'a2' ) -- The items in the listGROUP BY AHAVING COUNT( DISTINCT b ) = 2; -- The number of items in the listOutput:A-aQuery - Passing the list dynamically:CREATE OR REPLACE TYPE stringlist IS TABLE OF VARCHAR2(10);/SELECT AFROM table_nameWHERE B MEMBER OF :your_listGROUP BY AHAVING 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 AFROM table_nameGROUP BY AHAVING COUNT( DISTINCT CASE WHEN b IN ( 'a1', 'a2' ) THEN b END ) = 2AND COUNT( DISTINCT CASE WHEN b NOT IN ( 'a1', 'a2' ) THEN b END ) = 0;orSELECT AFROM table_nameGROUP BY AHAVING 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查询以匹配表中所有行中列表中的所有值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!