DB2 SQL：如何用单查询返回用户ID在多表的存在状态（1/0）

当然可以，以下两种常见场景的SQL写法供你参考：

场景1：查询指定单个用户ID的存在状态 #

如果要针对某个特定用户ID（示例中:target_userid为待查询的参数），可以结合EXISTS子查询与DB2内置虚表SYSIBM.SYSDUMMY1生成一行结果：

SELECT
    CASE WHEN EXISTS (SELECT 1 FROM t1 WHERE userid = :target_userid) THEN 1 ELSE 0 END AS t1_exists,
    CASE WHEN EXISTS (SELECT 1 FROM t2 WHERE userid = :target_userid) THEN 1 ELSE 0 END AS t2_exists,
    CASE WHEN EXISTS (SELECT 1 FROM t3 WHERE userid = :target_userid) THEN 1 ELSE 0 END AS t3_exists,
    CASE WHEN EXISTS (SELECT 1 FROM t4 WHERE userid = :target_userid) THEN 1 ELSE 0 END AS t4_exists
FROM SYSIBM.SYSDUMMY1;

EXISTS的优势是查询效率高，只要找到匹配记录就停止扫描表，适合数据量较大的场景。

场景2：查询所有出现过的用户ID的存在状态 #

如果要统计所有在任意表中出现过的用户ID的状态，先通过UNION收集所有唯一用户ID，再逐个判断各表的存在性：

WITH all_userids AS (
    SELECT userid FROM t1
    UNION
    SELECT userid FROM t2
    UNION
    SELECT userid FROM t3
    UNION
    SELECT userid FROM t4
)
SELECT
    au.userid,
    CASE WHEN EXISTS (SELECT 1 FROM t1 WHERE t1.userid = au.userid) THEN 1 ELSE 0 END AS t1_exists,
    CASE WHEN EXISTS (SELECT 1 FROM t2 WHERE t2.userid = au.userid) THEN 1 ELSE 0 END AS t2_exists,
    CASE WHEN EXISTS (SELECT 1 FROM t3 WHERE t3.userid = au.userid) THEN 1 ELSE 0 END AS t3_exists,
    CASE WHEN EXISTS (SELECT 1 FROM t4 WHERE t4.userid = au.userid) THEN 1 ELSE 0 END AS t4_exists
FROM all_userids au;

UNION会自动去重，确保每个用户ID只出现一次，避免重复统计。

注意：将上述SQL中的t1、t2、t3、t4替换为你实际的表名即可。

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