如何关联同表两个为NULL的值 实现两张表的直接JOIN查询

实现思路 #

你的核心需求是将子节点s_sym行和父节点存储的r_id关联，只需要先对存储s_sym、parent的表做自连接（多层级父子关系可改用递归CTE），把父节点的r_id传递给对应子节点，再用传递后的r_id关联第二张业务表即可。

以下示例中我们将存储s_sym、parent、r_id的表命名为symbol_table，存储r_id、gross的表命名为revenue_table。

单层级父子关系查询代码 #

如果你的父子关系只有一层（即父节点本身就是根节点，直接存储r_id），直接用自连接即可：

SELECT 
  parent_t.r_id,
  child_t.s_sym,
  child_t.parent,
  rev.gross
FROM symbol_table child_t
-- 自关联匹配子节点对应的父节点行，拿到父节点的r_id
LEFT JOIN symbol_table parent_t 
  ON child_t.parent = parent_t.s_sym
-- 用父节点的r_id关联业务表获取gross数据
LEFT JOIN revenue_table rev
  ON parent_t.r_id = rev.r_id
-- 可根据需求调整过滤条件，不加过滤会返回所有子节点行
WHERE child_t.s_sym = 'aaaa.BW';

多层级父子关系兼容方案 #

如果存在多级父子嵌套（比如A的父节点是B，B的父节点是C，只有C存储r_id），可以用递归CTE拉平整个层级树，把根节点的r_id传递到所有子节点：

WITH RECURSIVE symbol_hierarchy AS (
  -- 递归起始：查询所有根节点（parent为NULL，存储r_id的行）
  SELECT s_sym, parent, r_id
  FROM symbol_table
  WHERE parent IS NULL
  UNION ALL
  -- 递归迭代：关联子节点，继承父节点的r_id
  SELECT c.s_sym, c.parent, p.r_id
  FROM symbol_table c
  JOIN symbol_hierarchy p ON c.parent = p.s_sym
)
SELECT 
  sh.r_id,
  sh.s_sym,
  sh.parent,
  rev.gross
FROM symbol_hierarchy sh
LEFT JOIN revenue_table rev ON sh.r_id = rev.r_id;

该方案支持任意层级的父子关系，无需调整代码即可适配所有节点的r_id关联需求。

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