SQL查询实现返回B表所有ID并判断A表引用数是否大于1

你的实现方案逻辑是完全正确的，先分组统计每个a_id对应的关联条数是否大于1，再用左关联映射回B表的每一行，用COALESCE处理NULL值的逻辑也完全匹配需求。

如果你的数据库支持窗口函数，还可以用更简洁的写法，不需要嵌套子查询关联：

SELECT 
  id,
  COALESCE(COUNT(*) OVER (PARTITION BY a_id) > 1, false) AS more_than_one_reference
FROM b;

这个写法和你的方案执行结果完全一致，逻辑是对每个a_id分组统计行数，直接判断行数是否大于1，a_id为NULL的场景用COALESCE转成false即可，可读性更高。

原始问题背景 #

现有如下两张表：

A                 B
id                id   a_id
----              ---------
1                 1    1
2                 2    1
3                 3    2
                  4    2
                  5    2
                  6    3
                  7    NULL
                  8    NULL

需求 #

返回B表的所有ID，同时为每个ID返回对应关联A表的引用数是否大于1，预期结果如下：

result
B.id  more_than_one_reference
-----------------------------
1     true
2     true
3     true
4     true
5     true
6     false
7     false
8     false

统计规则 #

• B.id = 1、2返回true：B表中有2行数据的a_id = 1
• B.id = 3、4、5返回true：B表中有3行数据的a_id = 2
• B.id = 6返回false：B表中仅有1行数据的a_id = 3
• B.id = 7、8返回false：对应a_id值为NULL

提问者自行实现的方案 #

之前看到的采纳答案搭配where子句使用时会出错，自行实现的代码如下：

SELECT B.id, COALESCE(S1.more_than_one_reference, false)
FROM b B
         LEFT JOIN (SELECT B.a_id, COUNT(B.a_id) > 1 more_than_one_reference FROM b B GROUP BY B.a_id) S1
                   ON B.a_id = S1.a_id;

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