从一个表对任何列获取公共行 [英] get common rows from one table against any column

问题描述

我有三个表:

CREATE TABLE workflow_roles (
  role_id   NUMBER       PRIMARY KEY,
  role_desc VARCHAR2(20)
);

CREATE TABLE tbl_workflow (
  workflow_id   VARCHAR2(5)  PRIMARY KEY,
  workflow_desc VARCHAR2(20)
);

CREATE TABLE workflow_detail (
  role_id     NUMBER      REFERENCES workflow_roles( role_id ),
  workflow_id VARCHAR2(5) REFERENCES tbl_workflow( workflow_id )
);

让我们说数据是:

INSERT INTO workflow_roles
  SELECT 1, 'Role 1' FROM DUAL UNION ALL
  SELECT 2, 'Role 2' FROM DUAL;

INSERT INTO tbl_workflow
  SELECT 'A', 'Work A' FROM DUAL UNION ALL
  SELECT 'B', 'Work B' FROM DUAL UNION ALL
  SELECT 'C', 'Work C' FROM DUAL UNION ALL
  SELECT 'D', 'Work D' FROM DUAL UNION ALL

INSERT INTO workflow_detail
  SELECT 1, 'A' FROM DUAL UNION ALL
  SELECT 1, 'B' FROM DUAL UNION ALL
  SELECT 2, 'B' FROM DUAL UNION ALL
  SELECT 2, 'C' FROM DUAL;

两个工作流程中都存在角色"B"

我想获取所选工作流中存在的常见角色，即它应该仅返回角色B .

I want to get the common roles who exists in selected workflows i.e. it should return Role B only .

我尝试了以下方法:

select * from workflow_roles where role_id in 
(
select role_id from workflow_detail where workflow_id in (1,2)
);

但是它将返回分配给给定工作流程的所有角色.

But it is returning all roles assigned to the given workflows.

我该怎么做?

推荐答案

使用用户定义的聚合函数来获取多个集合的交集的解决方案:

A solution using a User-Defined Aggregation function to get the intersection of multiple collections:

Oracle设置:

首先，定义一个集合来保存工作流:

First, define a collection to hold the workflows:

CREATE OR REPLACE TYPE VARCHAR2s_Table IS TABLE OF VARCHAR2(4000);
/

第二，定义要在聚合过程中使用的对象:

Secondly, define an object to use in the aggregation process:

CREATE OR REPLACE TYPE Varchar2sTableIntersection AS OBJECT(
  intersection VARCHAR2s_Table,

  STATIC FUNCTION ODCIAggregateInitialize(
    ctx         IN OUT Varchar2sTableIntersection
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(
    self        IN OUT Varchar2sTableIntersection,
    value       IN     VARCHAR2s_Table
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN OUT Varchar2sTableIntersection,
    returnValue    OUT VARCHAR2s_Table,
    flags       IN     NUMBER
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(
    self        IN OUT Varchar2sTableIntersection,
    ctx         IN OUT Varchar2sTableIntersection
  ) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY Varchar2sTableIntersection
IS
  STATIC FUNCTION ODCIAggregateInitialize(
    ctx         IN OUT Varchar2sTableIntersection
  ) RETURN NUMBER
  IS
  BEGIN
    ctx := Varchar2sTableIntersection( NULL );
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(
    self        IN OUT Varchar2sTableIntersection,
    value       IN     VARCHAR2s_Table
  ) RETURN NUMBER
  IS
  BEGIN
    IF value IS NULL THEN
      NULL;
    ELSIF self.intersection IS NULL THEN
      self.intersection := value;
    ELSE
      self.intersection := self.intersection MULTISET INTERSECT value;
    END IF;
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN OUT Varchar2sTableIntersection,
    returnValue    OUT VARCHAR2s_Table,
    flags       IN     NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    returnValue := self.intersection;
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(
    self        IN OUT Varchar2sTableIntersection,
    ctx         IN OUT Varchar2sTableIntersection
  ) RETURN NUMBER
  IS
  BEGIN
    IF self.intersection IS NULL THEN
      self.intersection := ctx.intersection;
    ELSIF ctx.intersection IS NULL THEN
      NULL;
    ELSE
      self.intersection := self.intersection MULTISET INTERSECT ctx.intersection;
    END IF;
    RETURN ODCIConst.SUCCESS;
  END;
END;
/

第三，创建一个用户定义的聚合函数:

Thirdly, create a user-defined aggregation function:

CREATE FUNCTION MULTISET_INTERSECT( collection VARCHAR2s_Table )
RETURN VARCHAR2s_Table
PARALLEL_ENABLE AGGREGATE USING Varchar2sTableIntersection;
/

查询1-输出为集合:

SELECT MULTISET_INTERSECT( workflows ) AS common_workflows
FROM   (
  SELECT role_id,
         CAST(
           COLLECT(
             workflow_id
           ) AS VARCHAR2s_Table
         ) AS workflows
  FROM   workflow_detail
  GROUP BY role_id
);

输出:

COMMON_WORKFLOWS
----------------------
VARCHAR2S_TABLE( 'B' )

查询2-输出为行:

SELECT t.COLUMN_VALUE AS common_workflows
FROM   (
         SELECT MULTISET_INTERSECT( workflows ) AS common
         FROM   (
           SELECT role_id,
                  CAST(
                    COLLECT(
                      workflow_id
                    ) AS VARCHAR2s_Table
                  ) AS workflows
           FROM   workflow_detail
           GROUP BY role_id
         )
       ) cw
       CROSS JOIN TABLE( cw.common ) t;

输出:

COMMON_WORKFLOWS
----------------
B

这篇关于从一个表对任何列获取公共行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！