sql - 如何使用 IN 作为完全匹配使用 SQL？

Question

我有一个监控电话的系统。众所周知，手机可以有一个或两个 SIM 卡。众所周知，有不同的运营商。所以我想显示一个过滤器，其中包含电话和运营商的所有可能组合。今天，我拥有 1 万台设备。最终系统显示设备的测量，但用户可以通过手机制造商(苹果，三星，诺基亚，等等等等)，模型和运营商过滤那些统计数据。

所以我有这个表格，可以使用我的设备宇宙显示所有当前的组合。

最后我有这样的过滤器:

<select id="filter" multiple="multiple">
      <optgroup label="Model">
            <option value="1">iPhone</otion>
            <option value="2">Samsung</otion>
            <option value="3">Asus</otion>
      </optgroup>
      <optgroup label="Operator">
            <option value="1">Digicel</otion>
            <option value="2">FLOW</otion>
            <option value="3">Rogers</otion>
            <option value="4">Telus</otion>
            <option value="5">Bell</otion>
            ...
            ...
            <option value="2,3">FLOW,Rogers</otion>
            <option value="2,5">FLOW,Bell</otion>
            <option value="3,1">Rogers,Digicel</otion>
            ...[I don't know all current combinations..this is a dynamic filter]
      </optgroup>
</select>

数据库模型

所以我有设备的主表(我将只放置“重要”的列):

devices
------------   
id_device (pk) | id_manufacter (fk) | id_model (fk)
------------   
 1 | 1  | 10
 2 | 1  | 13
 3 | 1  | 14
 4 | 2  | 5
 4 | 2  | 6
 ..........
 99| 60 | 811
 ..........


operators
------------   
id_operator (pk) | operator_name | operator_ip
------------   
 1 | "Digicel"  | 10.192.112.29
 2 | "FLOW"     | 10.192.112.33
 3 | "Rogers"   | 10.192.112.54
 4 | "Telus"    | 10.192.112.111
 5 | "Bell"     | 10.192.112.233
 ..........
 4654 | "Vivo"     | 10.192.112.44
 ..........

我用来做一些测量的 IP 地址 - 现在无关紧要 - 当然这些是假 ip。

我有这个中间表:

------------   
id_device | id_operator
------------   
 1 | 1
 1 | 2
 2 | 1
 3 | 3
 4 | 2
 4 | 3
 5 | 2
 5 | 5
 6 | 2
 6 | 5
 .........
 129129 | 3
 129129 | 1

我想要的是:

一种方式或想法 - 如您所愿 - 通过“独占”选项进行过滤。如果我选择选项 <option value="2,5">FLOW,Bell</otion> ，它将返回具有此 SIM 组合的所有设备: 2,5 。在这种情况下 - 记录很少 - 设备的 id 5 和 6 应该被返回。

用户可选择

<option value="1">Digicel</otion>
<option value="3">Rogers</otion>
<option value="2,3">FLOW,Rogers</otion>
<option value="2,5">FLOW,Bell</otion>

在这种情况下，它应该返回所有只有 SIM 与运营商#1 或 SIM 与运营商#3 或 SIM 与运营商 #2 AND 运营商 #3 或 SIM 与运营商 #2 AND 运营商 #5 的所有设备:

devices #2,#3,#4,#5,#6.

在数据库中，我使用以下 function 创建了一个包:

FUNCTION generalMeasurements (
   models             IN   VARCHAR2,
   manufacturers      IN   VARCHAR2,
   idsoperators       IN   VARCHAR2
)
   RETURN sys_refcursor 

IS

vmanufacturers table_string := str2table(manufacturers);
vidmodels table_string := str2table(models);

cout sys_refcursor;

BEGIN
    open cout for
    select count(*), bla bla bla
      from devices inner join operators_device on id = id_device
        inner join operator on id_operator = operator_id
        WHERE (   (models IS NULL)
                  OR id_model IN (
                                         SELECT COLUMN_VALUE
                                           FROM TABLE
                                                     (vidmodels))
                 )
             AND (   (manufacturers IS NULL)
                  OR id_manufacturer IN (
                                            SELECT COLUMN_VALUE
                                              FROM TABLE
                                                        (vmanufacturers))
                 );

END;

CREATE OR REPLACE TYPE table_string IS TABLE OF VARCHAR2(30);

CREATE OR REPLACE FUNCTION str2table (p_str IN VARCHAR2)
   RETURN table_string
IS
   l_str    LONG         DEFAULT p_str || ',';
   l_n      NUMBER;
   l_data   table_string := table_string ();
BEGIN
   LOOP
      l_n := INSTR (l_str, ',');
      EXIT WHEN (NVL (l_n, 0) = 0);
      l_data.EXTEND;
      l_data (l_data.COUNT) := LTRIM (RTRIM (SUBSTR (l_str, 1, l_n - 1)));
      l_str := SUBSTR (l_str, l_n + 1);
   END LOOP;

   return l_data;
END;

那么......有什么想法可以做到这一点？

Answer 1

我已经添加了一个可以正常工作的答案，但后来发现 Oracle 有一个 aggregation function用于构建逗号分隔的列表。

这使得这个查询非常简单(作为奖励使用 IN 关键字!):

select  *
from    (
            select  id_device,
                    /* Create comma delimited list of operators for each device */
                    LISTAGG(id_operator, ',') 
                    WITHIN GROUP (ORDER BY id_operator) AS op_list
            from device_operators
            group by ID_device 
        ) As a
 where  op_list in ('1','2,3','2,5','3')

见 this SQLfiddle对于 Postgres 中的等效工作版本(Oracle 由于某种原因不在 SQLfiddle 中工作)。