SAP HANA中DWFR与DWFR3 Schema表对比：补充获取DWFR独有表

我来帮你搞定这个需求！你现在需要同时获取两类表：一是DWFR和DWFR3中都存在、且符合指定过滤规则的同名表，二是DWFR中存在但DWFR3中没有、同时也满足过滤条件的表。咱们可以通过UNION ALL把原有的匹配表查询和新增的独有表查询合并，一次得到所有结果。

优化后的简洁SQL（推荐） #

我用了WITH子句把重复的过滤条件统一抽取出来，这样后续要修改过滤规则只需要改一处，代码更易维护：

WITH filtered_tables AS (
    SELECT schema_name, table_name
    FROM tables
    WHERE table_name NOT LIKE 'RA%'
      AND table_name NOT LIKE 'ZZ%'
      AND table_name NOT LIKE 'DELETE%'
      AND table_name NOT LIKE '%TEST%'
      AND table_name NOT LIKE 'XX%'
      AND table_name NOT LIKE 'ROB%'
)
-- 第一部分：DWFR和DWFR3都存在的同名匹配表
SELECT schema_name, table_name, '匹配表' AS table_type
FROM filtered_tables
WHERE schema_name = 'DWFR'
  AND table_name IN (SELECT table_name FROM filtered_tables WHERE schema_name = 'DWFR3')
UNION ALL
-- 第二部分：DWFR独有且符合过滤条件的表
SELECT schema_name, table_name, 'DWFR独有表' AS table_type
FROM filtered_tables
WHERE schema_name = 'DWFR'
  AND table_name NOT IN (SELECT table_name FROM filtered_tables WHERE schema_name = 'DWFR3')
ORDER BY table_type, schema_name, table_name;

不用WITH子句的写法（适合不习惯CTE的场景） #

如果不想用公共表表达式，也可以把过滤条件直接写在两个查询块里，逻辑是一样的：

-- 匹配表部分
SELECT schema_name, table_name, '匹配表' AS table_type
FROM tables
WHERE schema_name = 'DWFR'
  AND table_name NOT LIKE 'RA%'
  AND table_name NOT LIKE 'ZZ%'
  AND table_name NOT LIKE 'DELETE%'
  AND table_name NOT LIKE '%TEST%'
  AND table_name NOT LIKE 'XX%'
  AND table_name NOT LIKE 'ROB%'
  AND table_name IN (
      SELECT table_name 
      FROM tables 
      WHERE schema_name = 'DWFR3'
        AND table_name NOT LIKE 'RA%'
        AND table_name NOT LIKE 'ZZ%'
        AND table_name NOT LIKE 'DELETE%'
        AND table_name NOT LIKE '%TEST%'
        AND table_name NOT LIKE 'XX%'
        AND table_name NOT LIKE 'ROB%'
  )
UNION ALL
-- DWFR独有表部分
SELECT schema_name, table_name, 'DWFR独有表' AS table_type
FROM tables
WHERE schema_name = 'DWFR'
  AND table_name NOT LIKE 'RA%'
  AND table_name NOT LIKE 'ZZ%'
  AND table_name NOT LIKE 'DELETE%'
  AND table_name NOT LIKE '%TEST%'
  AND table_name NOT LIKE 'XX%'
  AND table_name NOT LIKE 'ROB%'
  AND table_name NOT IN (
      SELECT table_name 
      FROM tables 
      WHERE schema_name = 'DWFR3'
        AND table_name NOT LIKE 'RA%'
        AND table_name NOT LIKE 'ZZ%'
        AND table_name NOT LIKE 'DELETE%'
        AND table_name NOT LIKE '%TEST%'
        AND table_name NOT LIKE 'XX%'
        AND table_name NOT LIKE 'ROB%'
  )
ORDER BY table_type, schema_name, table_name;

额外说明 #

我特意加了table_type字段，这样你在查看结果时能一眼区分出哪些是两个Schema共有的匹配表，哪些是DWFR独有的表，更直观。

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