时间戳

首页 > 解决方案 > 如何连接两个表 - 当您将用于 JOIN 的主键列中有多个值时

postgresql - 如何连接两个表 - 当您将用于 JOIN 的主键列中有多个值时

问题描述

我想加入以下两个表

用户：

用户身份 用户名
12 约翰
13 布赖恩
14 史蒂夫
15 巴里

访问：

用户身份 访问页面
用户_12 气体
用户_12，用户_14 dfvjk
用户_15 djvnjv
用户_14，用户_15 sdhjcb

预期成绩：

访问页面 用户名
气体 约翰
dfvjk 约翰；史蒂夫
djvnjv 巴里
sdhjcb 史蒂夫；巴里

通过使用以下查询，我得到了下面显示的结果

我写的查询：

SELECT visited_page, user_name
FROM VISITS
JOIN 
USERS on substring(user_id::VARCHAR FROM '[0-9]+')::INT = users.user_id

我得到的结果：

访问页面 用户名
气体 约翰
dfvjk 约翰
djvnjv 巴里
sdhjcb 史蒂夫

关于如何获得预期结果的任何建议？

标签： postgresql

解决方案

这是一个很好的例子，说明为什么存储一个逗号分隔的列表是一个非常非常糟糕的主意。这已经够糟糕了，你为什么要在 user_id 前面加上一个文本字符串？:(。第一件事是将visits.user_id展开到各个行，然后去掉前缀。结果是visits表本来应该是什么（当然少了强制性的FK）。

select (replace(user_id,'user_',''))::integer user_id,visited_page
  from ( select unnest(string_to_array(user_id, ',')) user_id, visited_page  
           from visits
       ) s;

有了它，我们可以将它加入用户，并按页面聚合用户名；给出最终查询：（参见演示）

select v.visited_page
     , string_agg( u.user_name, ',' order by u.user_name) users_visiting
  from users u
  join( select (replace(user_id,'user_',''))::integer user_id,visited_page
          from ( select unnest(string_to_array(user_id, ',')) user_id, visited_page  
                   from visits
                ) s
           ) v
      on (v.user_id = u.user_id) 
group by v.visited_page
order by v.visited_page;

建议：规范化表格，不要存储列表。这将导致更轻松的查询、更好的性能并提供数据完整性。考虑一下如果用户 id 被简单地转换为 41 而不是 14 并且用户 41 不存在（一个潜在的极其常见的错误），当前模式会发生什么。并且不要为数据值创建不必要的前缀来告诉您它是什么，名称已经这样做了 - 至少如果您选择了好的名称并且是一致的。但这些是不同日子的话题。

推荐阅读