Oracle表对比查询适配Sybase：替代COUNT(DISTINCT)窗口函数方案

针对你需要将Oracle表差异对比逻辑迁移至Sybase ASE和IQ的需求，以下是两种数据库的优化实现方案，替代Oracle中依赖COUNT(DISTINCT)窗口函数的逻辑：

核心需求回顾 #

基于主键对比两张表：

• 独有记录：row_cnt=1，仅存在于某一张表的主键记录
• 差异记录：row_cnt=2，主键存在于两张表，但字段值不同；无差异字段显示NULL，差异字段显示对应表的值

Sybase ASE 实现方案 #

ASE不支持COUNT(DISTINCT)窗口函数，我们可以通过拆分逻辑为主键计数和字段值聚合两个步骤实现：

-- Sybase ASE 优化版（支持ASE 16+，旧版本可替换CTE为临时表）
WITH pk_counts AS (
    -- 统计每个主键的来源表数量（替代COUNT(DISTINCT)窗口函数）
    SELECT pk_col1, pk_col2, COUNT(DISTINCT src) AS row_cnt
    FROM (
        SELECT pk_col1, pk_col2, 'TABLE_A' AS src FROM table_a
        UNION ALL
        SELECT pk_col1, pk_col2, 'TABLE_B' AS src FROM table_b
    ) t
    GROUP BY pk_col1, pk_col2
),
field_values AS (
    -- 聚合获取两张表的字段值
    SELECT 
        pk_col1, pk_col2,
        MAX(CASE WHEN src = 'TABLE_A' THEN col1 END) AS a_col1,
        MAX(CASE WHEN src = 'TABLE_B' THEN col1 END) AS b_col1,
        MAX(CASE WHEN src = 'TABLE_A' THEN col2 END) AS a_col2,
        MAX(CASE WHEN src = 'TABLE_B' THEN col2 END) AS b_col2
    FROM (
        SELECT pk_col1, pk_col2, col1, col2, 'TABLE_A' AS src FROM table_a
        UNION ALL
        SELECT pk_col1, pk_col2, col1, col2, 'TABLE_B' AS src FROM table_b
    ) t
    GROUP BY pk_col1, pk_col2
)
-- 最终筛选差异/独有记录
SELECT 
    f.pk_col1, f.pk_col2,
    CASE WHEN f.a_col1 != f.b_col1 OR (f.a_col1 IS NOT NULL AND f.b_col1 IS NULL) OR (f.a_col1 IS NULL AND f.b_col1 IS NOT NULL) THEN f.a_col1 END AS col1_diff_a,
    CASE WHEN f.a_col1 != f.b_col1 OR (f.a_col1 IS NOT NULL AND f.b_col1 IS NULL) OR (f.a_col1 IS NULL AND f.b_col1 IS NOT NULL) THEN f.b_col1 END AS col1_diff_b,
    CASE WHEN f.a_col2 != f.b_col2 OR (f.a_col2 IS NOT NULL AND f.b_col2 IS NULL) OR (f.a_col2 IS NULL AND f.b_col2 IS NOT NULL) THEN f.a_col2 END AS col2_diff_a,
    CASE WHEN f.a_col2 != f.b_col2 OR (f.a_col2 IS NOT NULL AND f.b_col2 IS NULL) OR (f.a_col2 IS NULL AND f.b_col2 IS NOT NULL) THEN f.b_col2 END AS col2_diff_b,
    pc.row_cnt
FROM field_values f
JOIN pk_counts pc ON f.pk_col1 = pc.pk_col1 AND f.pk_col2 = pc.pk_col2
WHERE pc.row_cnt = 1 
   OR (pc.row_cnt = 2 AND (
       f.a_col1 != f.b_col1 OR (f.a_col1 IS NOT NULL AND f.b_col1 IS NULL) OR (f.a_col1 IS NULL AND f.b_col1 IS NOT NULL)
       OR f.a_col2 != f.b_col2 OR (f.a_col2 IS NOT NULL AND f.b_col2 IS NULL) OR (f.a_col2 IS NULL AND f.b_col2 IS NOT NULL)
   ));

优势 #

• 逻辑拆分清晰，避免嵌套子查询的性能损耗
• 兼容ASE的SQL语法，无需依赖窗口函数

Sybase IQ 优化实现方案 #

IQ支持COUNT(DISTINCT)聚合函数，且对大数据量聚合性能优异，可简化实现逻辑：

-- Sybase IQ 高效版
SELECT 
    pk_col1, pk_col2,
    CASE WHEN a_col1 != b_col1 OR COALESCE(a_col1, '') != COALESCE(b_col1, '') THEN a_col1 END AS col1_diff_a,
    CASE WHEN a_col1 != b_col1 OR COALESCE(a_col1, '') != COALESCE(b_col1, '') THEN b_col1 END AS col1_diff_b,
    CASE WHEN a_col2 != b_col2 OR COALESCE(a_col2, '') != COALESCE(b_col2, '') THEN a_col2 END AS col2_diff_a,
    CASE WHEN a_col2 != b_col2 OR COALESCE(a_col2, '') != COALESCE(b_col2, '') THEN b_col2 END AS col2_diff_b,
    row_cnt
FROM (
    SELECT 
        pk_col1, pk_col2,
        MAX(CASE WHEN src = 'TABLE_A' THEN col1 END) AS a_col1,
        MAX(CASE WHEN src = 'TABLE_B' THEN col1 END) AS b_col1,
        MAX(CASE WHEN src = 'TABLE_A' THEN col2 END) AS a_col2,
        MAX(CASE WHEN src = 'TABLE_B' THEN col2 END) AS b_col2,
        COUNT(DISTINCT src) AS row_cnt -- 直接聚合统计来源数
    FROM (
        SELECT pk_col1, pk_col2, col1, col2, 'TABLE_A' AS src FROM table_a
        UNION ALL
        SELECT pk_col1, pk_col2, col1, col2, 'TABLE_B' AS src FROM table_b
    ) combined
    GROUP BY pk_col1, pk_col2
) agg
WHERE row_cnt = 1 
   OR (row_cnt = 2 AND (
       a_col1 != b_col1 OR (a_col1 IS NOT NULL AND b_col1 IS NULL) OR (a_col1 IS NULL AND b_col1 IS NOT NULL)
       OR a_col2 != b_col2 OR (a_col2 IS NOT NULL AND b_col2 IS NULL) OR (a_col2 IS NULL AND b_col2 IS NOT NULL)
   ));

优势 #

• 仅需一次扫描合并后的数据集，性能远优于嵌套子查询方案
• 利用IQ的列存储引擎特性，聚合效率极高

动态生成对比查询的Sybase存储过程（ASE/IQ通用） #

以下是适配Sybase的动态查询生成存储过程，可自动根据输入的表名、主键列、对比列生成差异查询：

CREATE PROCEDURE gen_table_compare_query
    @table_a VARCHAR(128),
    @table_b VARCHAR(128),
    @pk_cols VARCHAR(256), -- 逗号分隔主键列，如'pk_col1,pk_col2'
    @compare_cols VARCHAR(256) -- 逗号分隔对比列，如'col1,col2'
AS
BEGIN
    DECLARE @sql VARCHAR(8000)
    DECLARE @case_clauses VARCHAR(4000) = ''
    DECLARE @diff_conditions VARCHAR(4000) = ''
    DECLARE @agg_fields VARCHAR(4000) = ''

    -- 生成对比列的聚合、CASE语句和差异条件
    SELECT 
        @agg_fields = @agg_fields + 
            'MAX(CASE WHEN src = ''' + @table_a + ''' THEN ' + col + ' END) AS a_' + col + ',' + CHAR(10) +
            'MAX(CASE WHEN src = ''' + @table_b + ''' THEN ' + col + ' END) AS b_' + col + ',' + CHAR(10),
        @case_clauses = @case_clauses + 
            'CASE WHEN a_' + col + ' != b_' + col + ' OR (a_' + col + ' IS NOT NULL AND b_' + col + ' IS NULL) OR (a_' + col + ' IS NULL AND b_' + col + ' IS NOT NULL) THEN a_' + col + ' END AS ' + col + '_diff_a,' + CHAR(10) +
            'CASE WHEN a_' + col + ' != b_' + col + ' OR (a_' + col + ' IS NOT NULL AND b_' + col + ' IS NULL) OR (a_' + col + ' IS NULL AND b_' + col + ' IS NOT NULL) THEN b_' + col + ' END AS ' + col + '_diff_b,' + CHAR(10),
        @diff_conditions = @diff_conditions + 
            'a_' + col + ' != b_' + col + ' OR (a_' + col + ' IS NOT NULL AND b_' + col + ' IS NULL) OR (a_' + col + ' IS NULL AND b_' + col + ' IS NOT NULL) OR '
    FROM (
        SELECT LTRIM(RTRIM(value)) AS col
        FROM STRING_SPLIT(@compare_cols, ',') -- ASE/IQ 16+支持，旧版本需自定义拆分函数
    ) t

    -- 清理末尾多余的逗号和OR
    SET @agg_fields = LEFT(@agg_fields, LEN(@agg_fields) - 2)
    SET @case_clauses = LEFT(@case_clauses, LEN(@case_clauses) - 2)
    SET @diff_conditions = LEFT(@diff_conditions, LEN(@diff_conditions) - 3)

    -- 拼接完整SQL
    SET @sql = '
WITH pk_counts AS (
    SELECT ' + @pk_cols + ', COUNT(DISTINCT src) AS row_cnt
    FROM (
        SELECT ' + @pk_cols + ', ''' + @table_a + ''' AS src FROM ' + @table_a + '
        UNION ALL
        SELECT ' + @pk_cols + ', ''' + @table_b + ''' AS src FROM ' + @table_b + '
    ) t
    GROUP BY ' + @pk_cols + '
),
field_values AS (
    SELECT 
        ' + @pk_cols + ',' + CHAR(10) +
        @agg_fields + '
    FROM (
        SELECT ' + @pk_cols + ',' + @compare_cols + ', ''' + @table_a + ''' AS src FROM ' + @table_a + '
        UNION ALL
        SELECT ' + @pk_cols + ',' + @compare_cols + ', ''' + @table_b + ''' AS src FROM ' + @table_b + '
    ) t
    GROUP BY ' + @pk_cols + '
)
SELECT 
    ' + @pk_cols + ',' + CHAR(10) +
    @case_clauses + ',' + CHAR(10) +
    'pc.row_cnt
FROM field_values f
JOIN pk_counts pc ON ' + REPLACE(@pk_cols, ',', ' = pc.') + ' = f.' + REPLACE(@pk_cols, ',', ' AND pc.') + '
WHERE pc.row_cnt = 1 
   OR (pc.row_cnt = 2 AND (' + @diff_conditions + '))'

    PRINT @sql
    -- EXEC(@sql) -- 取消注释可直接执行生成的查询
END

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