如何从包含特定列的所有表中获取计数？ [英] How to get a count from all table containing a specific column?

问题描述

我有一个包含DB2数据库的IBM Content Manager。

在ICMADMIN中，我有一堆表，其中一些包含一个特定的列（让我们来调用它是ID_CLIENT），它是一个链接到一个表（CLIENT）的ID。

如何从每个包含ID_CLIENT列？

---

我知道如何检索包含ID_CLIENT的每个表的名称，但不知道如何加入客户端动态地。

 从syscat.columns中选择tabname其中colname ='ID_CLIENT'
  

（我们来调用这个查询A）

所以我的伪查询看起来像：

 从CLIENT连接（A）在CLIENT.ID_CLIENT =（A）.ID_CLIENT 
  

解决方案

可以通过两步过程来实现目标：

1. 查询y SYSCAT视图为CLIENT的每个潜在子表生成单独的SQL语句


2. 捕获并执行您生成的SQL

WITH ctbls（tbl）AS（
SELECT RTRIM（c.tabschema）|| ' c.tabname
FROM syscat.columns c
INNER JOIN syscat.tables t
ON t.tabschema = c.tabschema AND t.tabname = c.tabname
WHERE c.colname ='CLIENT_ID'
AND c.tabname<> 'CLIENT' - 我们不想将CLIENT加入到
AND t.type ='T' - 如果你想只使用表
AND c.typename ='INTEGER' - - 如果你只想要一个特定类型的$ CLIENT_ID列
）
- 在CLIENT和CTE返回的每个表之间构造一个左连接
SELECT'SELECT'''|| tbl
|| '''as childtablename，par.client_id，COUNT（*）AS childrows'
|| 'FROM client par LEFT OUTER JOIN'|| tbl || 'chd'
|| 'ON chd.client_id = par.client_id GROUP BY par.client_id;'
FROM ctbls
;


I have a IBM Content Manager with a DB2 database.

In ICMADMIN, I have a bunch of tables, and some of them contain a specific column (let's call it ID_CLIENT), which is an ID linking to one table (CLIENT).

How can I get the number of rows for each CLIENT from every table containing the ID_CLIENT column?

---

I know how to retrieve names of every table containing ID_CLIENT, but not how to join CLIENT on them dynamically.

select tabname from syscat.columns where colname='ID_CLIENT'

(let's call this query A)

So my pseudo query would look like:

select count(*) from CLIENT join (A) on CLIENT.ID_CLIENT = (A).ID_CLIENT

解决方案

It's possible to accomplish your goal by approaching it as a two-step process:

1. Query the SYSCAT views to generate a separate SQL statement for each potential child table of CLIENT
2. Capture and execute the SQL you generated

WITH ctbls ( tbl ) AS ( SELECT RTRIM( c.tabschema ) || '.' || c.tabname FROM syscat.columns c INNER JOIN syscat.tables t ON t.tabschema = c.tabschema AND t.tabname = c.tabname WHERE c.colname = 'CLIENT_ID' AND c.tabname <> 'CLIENT' -- we don't want to join CLIENT to itself AND t.type = 'T' -- if you want to work with tables only AND c.typename = 'INTEGER' -- if you want only want CLIENT_ID columns of a certain type ) -- Construct a left join between CLIENT and each table returned by the CTE above SELECT 'SELECT ''' || tbl || ''' AS childtablename, par.client_id, COUNT(*) AS childrows ' || 'FROM client par LEFT OUTER JOIN ' || tbl || ' chd ' || 'ON chd.client_id = par.client_id GROUP BY par.client_id;' FROM ctbls ;

这篇关于如何从包含特定列的所有表中获取计数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！