GROUP BY后使用HAVING子句的SQL查询结果异常问题咨询

你期望获取tiv_2015值重复出现的所有pid（如示例中的1、3、4），但现有两段SQL只返回pid=1，下面逐个分析问题原因，并解释SELECT与HAVING的工作机制，以及正确的实现方式。

现有SQL的问题分析 #

第一段SQL #

select pid 
from insurance 
group by tiv_2015
having count(*) > 1

这段SQL用tiv_2015分组，把相同tiv_2015的行归为一组，但SELECT pid时，每组可能包含多个不同的pid，数据库只会返回组内任意一个pid（不同数据库的具体返回规则有差异，比如取组内第一行），因此你只能拿到重复tiv_2015对应的其中一个pid，而非全部。

第二段SQL #

select pid 
from insurance 
having count(tiv_2015) > 1

这段SQL没有指定GROUP BY，相当于把整个表作为一个分组。count(tiv_2015) > 1仅判断全表中tiv_2015非空的行数大于1，但SELECT pid同样只会返回整个分组内的任意一个pid，结果自然不符合预期。

第三段SQL #

select tiv_2015 
from insurance 
having count(tiv_2015) > 1

同样是无GROUP BY的全局分组，count(tiv_2015) > 1成立时，返回的是全局分组内任意一个tiv_2015的值（刚好是重复的10），但这不是正确获取所有重复pid的方式。

SELECT、GROUP BY、HAVING的工作机制 #

1. 执行顺序：SQL的执行逻辑是：FROM/JOIN获取数据源 → WHERE过滤行 → GROUP BY分组 → HAVING过滤分组 → SELECT选择列 → ORDER BY排序。HAVING是在分组之后过滤分组，SELECT是最后选择要展示的列。
2. GROUP BY后的SELECT规则：使用GROUP BY时，SELECT中的列只能是两种：一是GROUP BY指定的分组列，二是被聚合函数（如count()、sum()）包裹的列。如果选择非分组且非聚合的列，数据库会返回该分组内的任意一个值（属于非确定性结果），无法拿到分组内的所有对应值。
3. 无GROUP BY的HAVING：没有GROUP BY时，全表视为一个分组，HAVING对这个整体分组过滤，SELECT的非聚合列仅返回任意一行的值。

正确获取目标pid的方法 #

要拿到所有tiv_2015重复的pid，确实需要通过外层查询实现，先找出所有重复的tiv_2015值，再关联原表获取对应的所有pid。推荐两种写法：

方法1：子查询关联 #

select pid
from insurance
where tiv_2015 in (
    -- 先找出所有重复的tiv_2015值
    select tiv_2015
    from insurance
    group by tiv_2015
    having count(*) > 1
);

方法2：窗口函数（更高效） #

select pid
from (
    -- 给每行标记其tiv_2015对应的重复次数
    select pid, 
           count(*) over (partition by tiv_2015) as tiv_count
    from insurance
) as temp
-- 筛选重复次数大于1的行
where tiv_count > 1;

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