Oracle SQL：查找同时包含名称含ID和NUM列的表的方法

问题背景 #

需要编写Oracle SQL语句，找出同时包含两类模糊匹配列名的表——例如同时存在列名含ID和列名含NUM的表。

原有尝试的SQL存在逻辑缺陷：

SELECT OWNER, TABLE_NAME, count(DISTINCT COLUMN_NAME) as ourCount 
FROM all_tab_cols 
WHERE (column_name LIKE '%ID%') OR (COLUMN_NAME LIKE '%NUM%') 
GROUP BY OWNER, TABLE_NAME 
HAVING COUNT(DISTINCT column_name) >= 2 
ORDER BY OWNER, TABLE_NAME ;

这段SQL会误筛选出仅包含多个同类别列的表（比如有3个含ID的列但没有含NUM的列），无法保证表同时具备两类目标列。

解决方案 #

通用Oracle SQL版本 #

通过条件聚合分别统计两类列的存在情况，确保两类列都至少有1个：

SELECT OWNER, TABLE_NAME
FROM all_tab_cols
GROUP BY OWNER, TABLE_NAME
HAVING SUM(CASE WHEN column_name LIKE '%ID%' THEN 1 ELSE 0 END) >= 1
   AND SUM(CASE WHEN column_name LIKE '%NUM%' THEN 1 ELSE 0 END) >= 1
ORDER BY OWNER, TABLE_NAME;

原理：对每一类模糊条件单独计数，只有当两类计数都≥1时，才判定该表符合要求，彻底避免单一类别多列的误判。

Toad环境变量版 #

如果在Toad中使用，可将匹配模式定义为变量，方便灵活修改查询条件：

SELECT OWNER, TABLE_NAME
FROM all_tab_cols
GROUP BY OWNER, TABLE_NAME
HAVING SUM(CASE WHEN column_name LIKE :COL_PATTERN1 THEN 1 ELSE 0 END) >= 1
   AND SUM(CASE WHEN column_name LIKE :COL_PATTERN2 THEN 1 ELSE 0 END) >= 1
ORDER BY OWNER, TABLE_NAME;

执行时Toad会弹出输入框，直接输入两个模糊匹配字符串（如%ID%、%NUM%）即可。

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