SELECT DISTINCT查询不返回唯一值显示全部记录 但COUNT(DISTINCT)结果正常

核心原因 #

DISTINCT 关键字的去重规则是作用于 SELECT 后紧跟的所有字段的组合，而非单独作用于某一个字段，两条查询的去重范围不一样才导致结果不一致。
举个简单的例子：假设存在两条记录 (张三, 1001, 123456) 和 (张三, 1002, 123456)，两者的BVN相同但ID不同，在第一条查询中会被判定为两条不同记录返回，但是在统计唯一BVN数量时只会被计为1次。

• 第一条查询选择了 NAME、ID、BVN 三个字段，只要这三个字段的组合值不完全相同，就会被判定为不同记录保留下来。你统计到符合条件的唯一 BVN 只有3个，但同一个 BVN 大概率对应了多个不同的 NAME 或者 ID，三者的组合存在14种不同的结果，所以最终返回14条记录。
• 第二条查询仅对 BVN 字段做去重统计，只计算不同的 BVN 数量，自然返回结果为3。

解决方案 #

如果要实现按 BVN 去重，每个唯一 BVN 仅返回一条对应记录，可以用窗口函数实现，示例代码如下：

SELECT NAME, ID, BVN
FROM (
    SELECT 
        NAME, 
        ID, 
        BVN,
        -- 按BVN分组，组内按ID排序取第一条，也可按业务需求调整排序规则
        ROW_NUMBER() OVER (PARTITION BY BVN ORDER BY ID) AS row_num
    FROM bank_trans 
    WHERE BVN NOT IN (SELECT DISTINCT bvn FROM taxpayers)
) AS temp
WHERE row_num = 1;

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