SQL JOIN关联两表替换Product分类字段无效值最优写法

API 探路者

2026-8-26

SQL最优写法方案

直接对三个分类字段分别提取ID后关联分类表即可，不需要做复杂的行转列操作，主键关联的查询性能最高，是最优实现。

核心逻辑

三个分类字段独立存储，分别做三次LEFT JOIN关联Category表即可，用LEFT JOIN是为了避免某条数据分类值格式异常、匹配不到分类时整行产品数据被过滤。
所有无效分类值的ID提取规则固定：字符串固定以~:/ID作为前缀拼接分类ID，只要截取这个固定串后面的数字，就能拿到和Category.id匹配的关联键。

可直接运行的参考SQL（MySQL版本）

SELECT
  p.id,
  p.name,
  c1.categories AS category1,
  c2.categories AS category2,
  c3.categories AS category3
FROM Product p
LEFT JOIN Category c1
  ON c1.id = CAST(SUBSTRING_INDEX(p.category1, '~:/ID', -1) AS UNSIGNED)
LEFT JOIN Category c2
  ON c2.id = CAST(SUBSTRING_INDEX(p.category2, '~:/ID', -1) AS UNSIGNED)
LEFT JOIN Category c3
  ON c3.id = CAST(SUBSTRING_INDEX(p.category3, '~:/ID', -1) AS UNSIGNED);

针对给出的样例数据，查询返回的三个分类字段会直接替换成Cloths & Accessories、Cloths、Shirts的有效值，和预期完全一致。

其他数据库适配

不同数据库仅需要调整字符串截取、类型转换的函数即可，关联逻辑完全一致：

PostgreSQL：截取函数替换为SPLIT_PART(字段名, '~:/ID', 2)，类型转换用字段值::INT
SQL Server：截取函数替换为SUBSTRING(字段名, CHARINDEX('~:/ID', 字段名) + 5, LEN(字段名))，再转INT类型
Oracle：直接用正则REGEXP_SUBSTR(字段名, '[0-9]+$')提取末尾的数字ID即可，不需要匹配固定分隔符，对脏数据兼容性更好。