Firebird如何在SELECT子查询中删除重复行？

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()

- LuZa

---

从这里开始，“SELECT DISTINCT，ORDER BY和GROUP BY基于BLOB ID而不是内容工作。 这使它们几乎没用...”接着说：“GROUP BY的行为很奇怪，如果它们相邻，则将相等的行分组在一起，但如果它们分开，则不会。” 因此，也许尝试GROUP BY id_user，list_g_user并查看是否有效。 - JNevill

为什么需要内连接？ - Mark Rotteveel

为什么不使用以下语句：select u.id_user, list(g.id_user) from users u join gen g on .. where ... group by u.id_user？ - user330315

Answer 1

正如JNevill所指出的，用于blob的distinct在Firebird中并没有按预期工作：它比较blob的id（指向blob的“指针”），而不是blob的内容，且list()生成一个blob。 为了解决这个问题，有几个可能的解决方案：

1. Cast the result of list() to a varchar so it can be compared correctly, eg:

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

这意味着列表的字符长度不应超过varchar长度（例如，对于此示例为100）。

2. Your use of inner join in the query without using anything from the joined table, seems to suggest you are using the join as an existence check only. Replace that join with an exists check:

   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)
   

3. As suggested by JNevill in the comments, you could try using group by id_user on the top-level query, but this might not always work as it relies on the blob ids being adjacent. And it is not a viable solution in situations where you can't use 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
   

- Mark Rotteveel

Answer 2

SELECT  DISTINCT u.id_user,
        CAST(SUBSTRING((SELECT LIST(g.id_user)
         from gen g
         where g.id_user=u.id_user
         GROUP BY id_user) FROM 1 FOR 1000) AS VARCHAR(1000)) as list_g_user
FROM users u
where  u.id_user = 1
  INNER JOIN ...

- simeonb