如何用SQL筛选test_table所有列中均存在的值？

我需要从test_table表中筛选出在a1、a2、a3、a4所有列中都出现过的值。现有表结构及数据如下：

例如值5和6在所有列中都有出现，期望得到如下结果：

请问有没有简洁的SQL写法实现该需求？

简洁SQL实现方案 #

方案1：利用集合交集（推荐，逻辑直观）

大多数关系型数据库（PostgreSQL、SQL Server、Oracle等）支持INTERSECT操作符，直接取四个列非0值的交集，结果就是在所有列都出现过的值：

SELECT a1 AS a FROM test_table WHERE a1 != 0
INTERSECT
SELECT a2 AS a FROM test_table WHERE a2 != 0
INTERSECT
SELECT a3 AS a FROM test_table WHERE a3 != 0
INTERSECT
SELECT a4 AS a FROM test_table WHERE a4 != 0;

方案2：UNION ALL分组统计（兼容所有数据库）

如果数据库不支持INTERSECT（如MySQL 8.0之前版本），可以将所有列的非0值合并后，统计每个值覆盖的列数，等于总列数（4）的即为目标值：

SELECT val AS a
FROM (
    SELECT a1 AS val, 'a1' AS col FROM test_table WHERE a1 != 0
    UNION ALL
    SELECT a2 AS val, 'a2' AS col FROM test_table WHERE a2 != 0
    UNION ALL
    SELECT a3 AS val, 'a3' AS col FROM test_table WHERE a3 != 0
    UNION ALL
    SELECT a4 AS val, 'a4' AS col FROM test_table WHERE a4 != 0
) AS all_vals
GROUP BY val
HAVING COUNT(DISTINCT col) = 4;

说明 #

• INTERSECT写法代码最简洁，逻辑清晰，优先使用。
• 两个方案都排除了0值，因为从数据来看0是占位符，不属于需要统计的目标值。

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