Oracle PLSQL如何统计多列中各动态值的出现次数

你的原有方案低效的根源是逐ID逐列发起SQL查询，产生大量数据库上下文切换开销，完全可以通过单次SQL扫描完成全部统计，避免PL/SQL循环和重复查询。

固定列数场景（Col1~Col4固定）最优方案 #

使用UNPIVOT实现列转行后直接分组统计，自动忽略空值，无需额外判断：

SELECT 
  id, 
  val, 
  COUNT(*) AS 出现次数
FROM 你的实际表名
UNPIVOT (
    val FOR col IN (Col1, Col2, Col3, Col4)
)
GROUP BY id, val
ORDER BY id, val;

针对你的示例数据，上述SQL输出结果如下：

如果需要补全取值出现0次的统计结果（比如ID为2的记录也显示A的出现次数为0），可以用以下写法：

WITH all_val AS (
    -- 提取所有列中出现过的非空取值
    SELECT DISTINCT val
    FROM 你的实际表名
    UNPIVOT (
        val FOR col IN (Col1, Col2, Col3, Col4)
    )
),
all_id AS (
    -- 提取所有需要统计的ID
    SELECT DISTINCT id FROM 你的实际表名
)
SELECT 
  t1.id, 
  t2.val, 
  NVL(t3.cnt, 0) AS 出现次数
FROM all_id t1
CROSS JOIN all_val t2
LEFT JOIN (
    SELECT id, val, COUNT(*) cnt
    FROM 你的实际表名
    UNPIVOT (
        val FOR col IN (Col1, Col2, Col3, Col4)
    )
    GROUP BY id, val
) t3 ON t1.id = t3.id AND t2.val = t3.val
ORDER BY t1.id, t2.val;

列数动态变化场景优化方案 #

如果Col1~ColN的列数不固定，动态SQL也只需拼接一次UNPIVOT的列列表，单次执行拿到全量结果，不需要逐行循环查询：

DECLARE
    v_cols VARCHAR2(1000);
    v_sql VARCHAR2(2000);
    TYPE res_rec IS RECORD(id NUMBER, val VARCHAR2(100), cnt NUMBER);
    TYPE res_tab IS TABLE OF res_rec;
    v_res res_tab;
BEGIN
    -- 拼接所有需要统计的列名，示例为动态生成Col1到ColN，可根据实际列规则调整
    SELECT LISTAGG('Col'||LEVEL, ',') WITHIN GROUP (ORDER BY LEVEL) 
    INTO v_cols
    FROM DUAL CONNECT BY LEVEL <= 4; -- 这里的4替换为实际列数即可

    v_sql := 'SELECT id, val, COUNT(*) cnt FROM 你的实际表名 UNPIVOT(val FOR col IN ('||v_cols||')) GROUP BY id, val';
    EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_res;

    -- 后续处理统计结果
    FOR i IN 1..v_res.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('ID:'||v_res(i).id||' 取值:'||v_res(i).val||' 次数:'||v_res(i).cnt);
    END LOOP;
END;
/

该方案效率和静态SQL相当，远高于逐行查询的实现。

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