如何查询具有相同的一组值的项目
我正在寻找一种有效的方式来查询具有相同值的项目。如何查询具有相同的一组值的项目
我有一个下表
C_1 C_2
--------
A 1
A 2
-------
B 1
B 2
B 3
-------
C 1
C 2
-------
D 1
D 2
D 3
-------
E 1
E 2
-------
F 0
F 2
我会选择C -1项目有确切同一组C_2元素作为给定项目的名单。
项目A,我将有
C
E
对于B项,我将有
D
怎么能在SQL(Oracle 10g中)来完成?
下面是用于测试目的,创建表语句
create table t (c_1 varchar2(1), c_2 number);
INSERT into t VALUES('A', 1);
INSERT into t VALUES('A', 2);
INSERT into t VALUES('B', 1);
INSERT into t VALUES('B', 2);
INSERT into t VALUES('B', 3);
INSERT into t VALUES('C', 1);
INSERT into t VALUES('C', 2);
INSERT into t VALUES('D', 1);
INSERT into t VALUES('D', 2);
INSERT into t VALUES('D', 3);
INSERT into t VALUES('E', 1);
INSERT into t VALUES('E', 2);
INSERT into t VALUES('F', 0);
INSERT into t VALUES('F', 2);
您可以使用10g的COLLECT
函数;既然你不想看到c_2值是什么,你甚至不需要cast
它。
select c_1
from t
where c_1 != 'A'
group by c_1
having collect(c_2) = (select collect(c_2) from t where c_1 = 'A' group by c_1)
/
你需要什么?可以用除法来实现:
http://en.wikipedia.org/wiki/Relational_algebra#Division_.28.C3.B7.29
或类似的问题:
不幸的是,与关系代数一样,分隔也不像SQL那么容易表达。
是这样的:
SELECT DISTINCT c_1
FROM (SELECT c_1, c_2, COUNT(*) over(PARTITION BY c_1) cnt FROM t)
WHERE (SELECT MAX(c_2) FROM t WHERE c_1 = 'A') = cnt
AND c_1 <> 'A'
不知道的性能,你可以尝试otpimize了一点,告诉我,如果它的工作原理
我认为,只有当'c_2'值是连续的,所有从一开始。如果你敲出'D,2',那么'A'的结果中包含'D',因为'A'的最高值与'D'的值的数目相匹配 - 不是完全相同的一组值。 – 2011-06-15 11:04:16
感谢您的回答,但只有在c_2中的值是连续的时才有效。我对C_2中的值分布一无所知。我在我的问题中添加了“F”项的行。你查询也会返回“F”。但是“F”并不具有与“A”完全相同的c_2值集合 – schurik 2011-06-15 11:07:23
SELECT
[search].c_1,
[match].c_1
FROM
t AS [search]
INNER JOIN
t AS [match]
ON [search].c_2 = [match].c_2
GROUP BY
[search].c_1,
[match].c_1
HAVING
COUNT(*) = (SELECT COUNT(*) FROM t WHERE c_1 = [search].c_1)
AND COUNT(*) = (SELECT COUNT(*) FROM t WHERE c_1 = [match].c_1)
为了获得最大的性能,你需要两个索引。一个在c_1
和另一个在c_2
。
- c_1
上的索引使两个SELECT COUNT(*)
查询简单索引查找。
- c_2
上的索引使联接成为查找对象。
你可以添加AND [search].c_1 != [match].c_1
停止了一套匹配自己。
create type t_c_2 as table of number;
select one.c_1, two.c_1
from (select t.c_1, CAST(COLLECT(t.c_2) as t_c_2) coll
from t
group by t.c_1) one
,(select t.c_1, CAST(COLLECT(t.c_2) as t_c_2) coll
from t
group by t.c_1) two
where one.coll = two.coll
and one.c_1 != two.c_1;
我从来没有在生产现场使用过这样的结构,需要进行效率验证。
我不是甲骨文的专家,所以感谢您向我介绍COLLECT和嵌套表格:)我会赞成,但Marcink的答案更通用... – MatBailie 2011-06-15 11:07:48
谢谢,这就是我正在寻找 – schurik 2011-06-15 12:51:12
这是一个非常好的主意。有时候那些关系型扩展确实很整齐。 – 2011-06-17 07:52:16