如何查询具有相同值集的项目(How to query items with identical set of values)

如何查询具有相同值集的项目(How to query items with identical set of values)

我正在寻找一种有效的方法来查询具有相同值集的项目。

我有一张桌子

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

我将选择FROM C_1中与给定项具有完全相同的c_2元素集的项列表。

对于项目A我会有

C E

对于项目B，我将有

D

如何在SQL（Oracle 10g）中完成？

---

这是用于测试目的的create table语句

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);

I'm looking for an efficient way to query items, that have identical set of values.

I have a following table

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

I will select the list of items FROM C_1 that have exact the same set of c_2 elements as the given item.

For item A i will have

C E

For item B i will have

D

how can it be done in SQL( Oracle 10g )?

---

Here is the create table statement for test purposes

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值是什么，你甚至不需要强制转换它。

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) /

You can use 10g's COLLECT function; since you don't want to see what the c_2 values are you don't even need to cast it.

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) /