如何在JOIN所用主键列存在多值时关联两张SQL表得到预期结果

问题原因 #

你现有写法的问题是substring(user_id::VARCHAR FROM '[0-9]+')只会提取整个user_id字段中第一个匹配的数字串，遇到逗号分隔的多用户标识时，只能拿到第一个用户的ID，自然只能匹配到第一个用户名。

解决思路 #

1. 先将VISITS表中逗号分隔的用户标识拆分为单行单个用户标识
2. 对拆分后的单个用户标识提取数字部分，关联USERS表拿到用户名
3. 按visited_page分组，将同页面的用户名用分号拼接输出

不同数据库实现示例 #

PostgreSQL 写法 #

SELECT 
  v.visited_page,
  string_agg(u.user_name, ';' ORDER BY u.user_id) AS user_name
FROM (
  -- 拆分逗号分隔的user_id为单行
  SELECT 
    visited_page,
    unnest(string_to_array(user_id, ',')) AS single_user_id
  FROM VISITS
) v
JOIN USERS u 
  ON substring(v.single_user_id FROM '[0-9]+')::INT = u.user_id
GROUP BY v.visited_page
ORDER BY v.visited_page;

MySQL 8.0+ 写法 #

WITH RECURSIVE split_ids AS (
  SELECT 
    visited_page,
    user_id AS remaining_ids,
    NULL AS single_user_id
  FROM VISITS
  UNION ALL
  SELECT 
    visited_page,
    IF(INSTR(remaining_ids, ',') = 0, '', SUBSTRING(remaining_ids, INSTR(remaining_ids, ',') + 1)),
    SUBSTRING_INDEX(remaining_ids, ',', 1)
  FROM split_ids
  WHERE remaining_ids != ''
)
SELECT 
  s.visited_page,
  GROUP_CONCAT(u.user_name ORDER BY u.user_id SEPARATOR ';') AS user_name
FROM split_ids s
JOIN USERS u 
  ON CAST(REPLACE(s.single_user_id, 'user_', '') AS UNSIGNED) = u.user_id
WHERE s.single_user_id IS NOT NULL
GROUP BY s.visited_page
ORDER BY s.visited_page;

Hive/Spark SQL 写法 #

SELECT 
  v.visited_page,
  concat_ws(';', collect_list(u.user_name)) AS user_name
FROM VISITS v
LATERAL VIEW explode(split(v.user_id, ',')) t AS single_user_id
JOIN USERS u 
  ON cast(regexp_extract(t.single_user_id, '[0-9]+', 0) AS INT) = u.user_id
GROUP BY v.visited_page
ORDER BY v.visited_page;

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