执行SQL select distinct时排除BLOB字段的替代方案咨询

报错原因 #

你遇到的语法报错本质是所用数据库不支持窗口函数（比如Firebird 2.1及更低版本、部分老旧数据库版本都不支持分析函数语法），或是直接用了Table这种SQL关键字作为表名未做转义，和去重逻辑本身无关。

兼容全版本的替代解决方案 #

方案1：非BLOB字段先去重再关联取BLOB值

该方案无需窗口函数支持，所有关系型数据库通用，逻辑是先拿到去重后的非BLOB字段组合，再关联原表取对应BLOB值，同一个非BLOB字段组合默认取最早插入的一条，你也可以调整子查询条件指定取值规则：

SELECT t1.Field1, t1.Field2, t1.Field3, t2.Blob1, t2.Blob2
FROM (
  -- 第一步：对需要去重的非BLOB字段做去重
  SELECT DISTINCT Field1, Field2, Field3
  FROM 实际表名
) t1
-- 关联原表取对应BLOB字段，通过主键/唯一键确定每个分组取哪条记录
JOIN 实际表名 t2 
  ON t2.主键字段 = (
    SELECT MIN(主键字段) 
    FROM 实际表名 t3 
    WHERE t3.Field1 = t1.Field1 
      AND t3.Field2 = t1.Field2 
      AND t3.Field3 = t1.Field3
  )

如果表没有主键，可以用对应数据库的内置伪列替代：Oracle用ROWID、Firebird用RDB$DB_KEY、无主键的MySQL表可以用自定义变量生成唯一行号。

方案2：GROUP BY+聚合函数（适用支持BLOB聚合的数据库）

如果所用数据库支持BLOB类型的聚合函数，可使用更简洁的写法：

-- MySQL示例，用ANY_VALUE取分组内任意BLOB值
SELECT Field1, Field2, Field3, ANY_VALUE(Blob1) Blob1, ANY_VALUE(Blob2) Blob2
FROM 实际表名
GROUP BY Field1, Field2, Field3

-- Oracle 12c+示例，支持对BLOB字段做MAX聚合
SELECT Field1, Field2, Field3, MAX(Blob1) Blob1, MAX(Blob2) Blob2
FROM 实际表名
GROUP BY Field1, Field2, Field3

补充提示 #

如果你用的是Firebird数据库，升级到3.0及以上版本后，你最开始写的窗口函数语法本身是可用的，只要把关键字Table替换为实际表名、或者给关键字加转义符即可。

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