oracle - Oracle - SQL 从具有特定列和值的所有表中获取数据

Question

我有十几个带有 COLUMN_NAME 的连接表，ENTITY_ID我想找到所有具有特定值的相关记录，我可以找到具有特定列的表

select * from all_tab_cols a where a.COLUMN_NAME='ENTITY_ID';

我可以找到每张桌子的记录

select * from TABLENAME where ENTITY_ID='100';

有没有办法使用单个 SQL 查看所有表中的所有相关记录？

标签： oracle

Answer 1

我不确定会发生什么，因此根据我的理解回答如下：

• 假设我们有一个表ACCOUNT，其CUST_ID列如下：

SQL> SELECT OWNER, A.TABLE_NAME, COLUMN_NAME FROM
  2      ALL_TAB_COLS A
  3  WHERE
  4      A.COLUMN_NAME = 'CUST_ID';

OWNER      TABLE_NAME           COLUMN_NAME
---------- -------------------- --------------------
TEJASH     ACCOUNT              CUST_ID

SQL> SELECT * FROM ACCOUNT;

    ACC_NR       SUM_    CUST_ID
---------- ---------- ----------
       500       3400        100

• 现在，我想在所有表（我可以访问的表）中搜索，以找到所有表中都有一个CUST_ID包含值的列100。

SQL> SELECT
  2      table_name, COLUMN_NAME,
  3      to_number(xmlquery('/ROWSET/ROW/C/text()'
  4      passing xmltype(dbms_xmlgen.getxml(
  5        'select count(1) as c '
  6        || 'from "' || owner || '"."' || table_name || '" WHERE ' || COLUMN_NAME  || '=''100'''))
  7    returning content)) as cOUNT
  8  FROM ALL_TAB_COLS A
  9  WHERE A.COLUMN_NAME = 'CUST_ID';

TABLE_NAME           COLUMN_NAME               COUNT
-------------------- -------------------- ----------
ACCOUNT              CUST_ID                       1

CUST_ID在这里，将出现每个带有该列的表，并且COUNT列显示该表中的记录数CUST_ID = 100

• 现在，让我们在另一个表中添加一列，看看效果：

SQL> ALTER TABLE ACTIVE_USERS ADD CUST_ID VARCHAR2(100);

Table altered.

SQL> INSERT INTO ACTIVE_USERS VALUES (5,SYSDATE, SYSDATE, 200);

1 row created.

SQL> SELECT * FROM ACTIVE_USERS;

  CUST_NUM START_DATE           END_DATE             CUST
---------- -------------------- -------------------- ----
########## 21-NOV-19            21-NOV-19            200
########## 21-NOV-19            21-NOV-19
########## 01-JAN-18            01-JAN-19
########## 01-JAN-18
########## 01-JAN-19            01-JUN-19
########## 01-JAN-17            01-MAR-19

6 rows selected.

• 现在，再次运行我们的查询以从所有表中查找数据：

SQL> SELECT
  2      table_name, COLUMN_NAME,
  3      to_number(xmlquery('/ROWSET/ROW/C/text()'
  4      passing xmltype(dbms_xmlgen.getxml(
  5        'select count(1) as c '
  6        || 'from "' || owner || '"."' || table_name || '" WHERE ' || COLUMN_NAME  || '=''100'''))
  7    returning content)) as cOUNT
  8  FROM ALL_TAB_COLS A
  9  WHERE A.COLUMN_NAME = 'CUST_ID';

TABLE_NAME           COLUMN_NAME               COUNT
-------------------- -------------------- ----------
ACCOUNT              CUST_ID                       1
ACTIVE_USERS         CUST_ID                       0

SQL>

而且，它奏效了！！

• 再次向ACTIVE_USERS表中添加更多数据并查看结果。

SQL> INSERT INTO ACTIVE_USERS VALUES (6,SYSDATE-1, SYSDATE, 100);

1 row created.

SQL> INSERT INTO ACTIVE_USERS VALUES (7,SYSDATE-2, SYSDATE, 100);

1 row created.

SQL> INSERT INTO ACTIVE_USERS VALUES (8,SYSDATE-3, SYSDATE, 100);

1 row created.

SQL> SELECT * FROM ACTIVE_USERS;

  CUST_NUM START_DATE           END_DATE             CUST
---------- -------------------- -------------------- ----
########## 21-NOV-19            21-NOV-19            200
########## 20-NOV-19            21-NOV-19            100
########## 19-NOV-19            21-NOV-19            100
########## 18-NOV-19            21-NOV-19            100
########## 21-NOV-19            21-NOV-19
########## 01-JAN-18            01-JAN-19
########## 01-JAN-18
########## 01-JAN-19            01-JUN-19
########## 01-JAN-17            01-MAR-19

9 rows selected.

• 现在让我们检查一下查询的结果。

SQL> SELECT
  2      table_name, COLUMN_NAME,
  3      to_number(xmlquery('/ROWSET/ROW/C/text()'
  4      passing xmltype(dbms_xmlgen.getxml(
  5        'select count(1) as c '
  6        || 'from "' || owner || '"."' || table_name || '" WHERE ' || COLUMN_NAME  || '=''100'''))
  7    returning content)) as cOUNT
  8  FROM ALL_TAB_COLS A
  9  WHERE A.COLUMN_NAME = 'CUST_ID';

TABLE_NAME           COLUMN_NAME               COUNT
-------------------- -------------------- ----------
ACCOUNT              CUST_ID                       1
ACTIVE_USERS         CUST_ID                       3

SQL>

再次，它工作！:)

干杯！！