如何用聚合函数替代左连接找出缺失creator列的c_alertaction表

你可以通过条件聚合来达成需求，不用左连接就能同时返回所有包含c_alertaction表的schema，以及每个schema中creator列的存在状态：

写法一：用MAX+CASE判断 #

SELECT 
  table_schema,
  CASE 
    WHEN MAX(CASE WHEN column_name = 'creator' THEN 1 ELSE 0 END) = 1 
    THEN '存在creator列' 
    ELSE '缺失creator列' 
  END AS creator_column_status
FROM information_schema.columns
WHERE table_name = 'c_alertaction'
GROUP BY table_schema;

写法二：用COUNT+CASE判断（更直观） #

SELECT 
  table_schema,
  CASE 
    WHEN COUNT(CASE WHEN column_name = 'creator' THEN 1 END) > 0 
    THEN '存在creator列' 
    ELSE '缺失creator列' 
  END AS creator_column_status
FROM information_schema.columns
WHERE table_name = 'c_alertaction'
GROUP BY table_schema;

逻辑说明 #

1. 先通过WHERE table_name = 'c_alertaction'锁定所有目标表的列信息，由于所有schema的这个表都包含oid列，分组后自然能覆盖所有存在该表的schema。
2. 分组后通过条件聚合做状态判断：
   • 写法一中，只要分组内存在creator列，MAX(CASE...)就会返回1，否则返回0，以此区分列的存在状态。
   • 写法二中，COUNT(CASE...)仅统计列名为creator的记录数，结果大于0就说明该schema的表包含此列。

如果需要更简洁的输出（比如用1/0表示存在/缺失），可以直接返回聚合结果：

SELECT 
  table_schema,
  MAX(CASE WHEN column_name = 'creator' THEN 1 ELSE 0 END) AS has_creator_column
FROM information_schema.columns
WHERE table_name = 'c_alertaction'
GROUP BY table_schema;

内容的提问来源于stack exchange，提问作者Tom Melly