我们知道,如果有两个结果集,需要求交集的话,可以使用 INTERSECT 这个关键字,但如果是一个结果集,按照某个规则分组,然后求各组之间交集,这条sql语句,该如何写呢。脚本如下
create table t_test200(id1 varchar2(16),id2 varchar2(16),value1 varchar2(32),value2 varchar2(32),value3 varchar2(32));
--A组
insert into t_test200(id1,id2,value1,value2,value3) values('a','a','000','111','222');
insert into t_test200(id1,id2,value1,value2,value3) values('a','a','111','222','333');
insert into t_test200(id1,id2,value1,value2,value3) values('a','a','222','333','444');
--B组
insert into t_test200(id1,id2,value1,value2,value3) values('b','b','000','111','222');
insert into t_test200(id1,id2,value1,value2,value3) values('b','b','333','444','555');
insert into t_test200(id1,id2,value1,value2,value3) values('b','b','444','555','666');
--C组
insert into t_test200(id1,id2,value1,value2,value3) values('c','c','000','111','222');
insert into t_test200(id1,id2,value1,value2,value3) values('c','c','555','666','777');
insert into t_test200(id1,id2,value1,value2,value3) values('c','c','666','777','888');
--D组
insert into t_test200(id1,id2,value1,value2,value3) values('d','d','666','777','888');
insert into t_test200(id1,id2,value1,value2,value3) values('d','d','777','888','999');
insert into t_test200(id1,id2,value1,value2,value3) values('d','d','888','999','aaa');
数据如上面,按照id1和id2分组,交集取value1,value2,value3。A组∩B组∩C组,结果是'000','111','222'。C组∩D组,结果集是'666','777','888'。A组∩B组∩C组∩D组,结果集是空。
请问这条sql语句,该如何写呢?
谢谢。
|