sql - Firebird 删除 SELECT LIST 子查询上的重复行

Question

我使用的是 Firebird 数据库 v2.5，我无法从我的查询中删除重复的行:

SELECT  DISTINCT u.id_user, 
        (SELECT LIST(g.id_user) 
         from gen g 
         where g.id_user=u.id_user 
         GROUP BY id_user) as list_g_user
FROM users u 
where  u.id_user = 1 
  INNER JOIN ...

这是我的结果:

 id_user  |  list_g_user 
===================================
1         |  437,499,718,739,835,865
1         |  437,499,718,739,835,865
1         |  437,499,718,739,835,865
1         |  437,499,718,739,835,865

第二列结果 list_g_user 是 BLOB 类型。

相同的结果和多行。谁能解释一下为什么？

这是函数引用:Firebird Documentation: LIST()

Answer 1

正如 JNevill 已经指出的那样, distinct 与 blob does not behave as expected in Firebird :它比较 blob id(指向 blob 的“指针”)，而不是 blob 的内容，和 list()产生一个 blob。

要解决此问题，有几种可能的解决方案:

1. 将 list() 的结果转换为 varchar 以便正确比较，例如:

   SELECT cast(LIST(g.id_user) as varchar(100)) ...
   

然而，这确实意味着列表的字符长度不应超过 varchar 长度(本例中为 100)。

1. 您在查询中使用了 inner join 而没有使用连接表中的任何内容，这似乎表明您仅将连接用作存在性检查。将连接替换为 exists 检查:

   SELECT u.id_user, 
           (SELECT LIST(g.id_user) 
            from gen g 
            where g.id_user=u.id_user 
            GROUP BY id_user) as list_g_user
   FROM users u 
   where  u.id_user = 1 
   and exists (select * from <currently joined table> x where x.id_user = u.id_user)
   

2. 正如 JNevill 在评论中所建议的那样，您可以尝试在顶级查询中使用 group by id_user，但这可能并不总是有效，因为它依赖于相邻的 blob id .在无法使用 group by 的情况下，这不是一个可行的解决方案:

   SELECT  DISTINCT u.id_user, 
           (SELECT LIST(g.id_user) 
            from gen g 
            where g.id_user=u.id_user 
            GROUP BY id_user) as list_g_user
   FROM users u 
   where  u.id_user = 1 
     INNER JOIN ...
   group by u.id_user