我有各种模式,其中包含一个名为c_lartaction的表.在某些模式中,该表包含名为"CREATOR"列,但在其他模式中,缺少该列.
我可以相当容易地看到左连接的运行状态(所有实例都有一个‘OID’列):
select c1.table_schema, c1.table_name, c1.column_name,
c2.table_schema, c2.table_name, c2.column_name
from information_schema.columns as c1
left join information_schema.columns as c2 on
c1.table_schema = c2.table_schema
and c1.table_name = c2.table_name
and c2.column_name = 'creator'
where c1.table_name = 'c_alertaction'
and c1.column_name = 'oid';
然而,我确信我可以使用聚合函数在没有左连接的情况下完成这项工作,但到目前为止,我被难住了.
以下是我的try :
select c.table_schema, count(*) as CreatorCount
from information_schema.columns as c
where c.table_name = 'c_alertaction'
and c.column_name = 'creator'
group by c.table_schema;
但这将只报告具有该列的架构.
有什么建议/线索/pip 吗?