SQL按关键词匹配拆分为多行且保留无匹配记录实现咨询

实现方案 #

核心逻辑 #

你当前使用内连接仅返回匹配成功的记录，要保留所有访问日志记录，只需将连接改为左外连接，并将log_cities（访问日志表）作为左表即可，未匹配到城市的日志记录会自动将state字段置为NULL，完全符合需求。

MSSQL 版本实现（当前测试场景可用） #

SELECT l.user_id,
       c.state,
       l.visited_log
FROM log_cities l
LEFT JOIN city_lookup c 
  ON l.visited_log LIKE CONCAT('%', c.city, '%')
ORDER BY l.user_id, c.state

执行后输出和你给出的期望结果完全一致，无匹配的user_id=999的记录会正常保留。

BigQuery 版本实现（生产环境可用） #

BigQuery语法兼容上述写法，也可以用性能更优的写法适配大数据量场景：

SELECT 
  l.user_id,
  c.state,
  l.visited_log
FROM `你的项目名.你的数据集名.log_cities` l
LEFT JOIN `你的项目名.你的数据集名.city_lookup` c
  ON STRPOS(l.visited_log, c.city) > 0 -- STRPOS返回子串位置，大于0即表示包含对应城市，匹配效率高于LIKE
ORDER BY user_id, state

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