如何统计表A多列中特定元素(x,y,z)的每行出现次数？

数据老陈

2026-8-9

解决方案

核心思路是逐列判断当前值是否属于{x, y, z}集合，符合条件记为1，否则记为0，最后将三列的判断结果相加，得到每行的目标元素出现次数。

MySQL / MariaDB 实现

SELECT
    bisac1,
    bisac2,
    bisac3,
    desire,
    (
        CASE WHEN bisac1 IN ('x', 'y', 'z') THEN 1 ELSE 0 END +
        CASE WHEN bisac2 IN ('x', 'y', 'z') THEN 1 ELSE 0 END +
        CASE WHEN bisac3 IN ('x', 'y', 'z') THEN 1 ELSE 0 END
    ) AS xyz_count
FROM 表A;

PostgreSQL 实现

利用PostgreSQL内置的bool_to_int函数简化逻辑：

SELECT
    bisac1,
    bisac2,
    bisac3,
    desire,
    bool_to_int(bisac1 IN ('x', 'y', 'z')) +
    bool_to_int(bisac2 IN ('x', 'y', 'z')) +
    bool_to_int(bisac3 IN ('x', 'y', 'z')) AS xyz_count
FROM 表A;

SQL Server 实现

用IIF函数简化条件判断：

SELECT
    bisac1,
    bisac2,
    bisac3,
    desire,
    IIF(bisac1 IN ('x', 'y', 'z'), 1, 0) +
    IIF(bisac2 IN ('x', 'y', 'z'), 1, 0) +
    IIF(bisac3 IN ('x', 'y', 'z'), 1, 0) AS xyz_count
FROM 表A;