SQL中从子记录派生父记录的查询问题排查

我帮你仔细梳理了你的需求、原始数据和写的SQL查询，发现了几个关键问题，下面逐一说明：

原始数据表格 #

期望输出结果 #

你的原始查询语句 #

-- Parent Select
c1.PARENT_ID as ID, Parent_Id, c1.Child_ID as Child_1 c2.Child_ID as Child_2 c1.Identifier 
from sampletable as c1 
left join sampletable as c2 on c2.PARENT_ID = c1.PARENT_ID and c2.Identifier = 'OUT' 
where c1.Identifier = 'IN' 
UNION 
-- CHILD Select
child_id as ID, Parent_id, 
CASE when Identifier = 'IN' then Child_ID Else NULL END As Child_1, 
CASE when Identifier = 'OUT' then Child_ID Else NULL END As Child_2, 
Identifier from sampletable where parent_id is not null

查询中的错误点 #

• 父查询字段列表语法错误：字段之间缺少逗号分隔！c1.Child_ID as Child_1和c2.Child_ID as Child_2、c1.Identifier之间必须用逗号隔开，否则SQL引擎会直接抛出语法错误。
• 父查询的Parent_ID取值错误：按照期望结果，父记录的Parent_ID应该是NULL，但你现在直接写Parent_Id（实际会取c1.Parent_Id），这会导致父记录的Parent_ID和子记录的Parent_ID一致，不符合需求。
• 子查询的Child_1/Child_2逻辑错误：期望中子记录的Child_1和Child_2都是NULL，但你的子查询会把IN类型的子记录的Child_1设为自身ID，OUT类型的设为自身ID，和需求不符。
• UNION的潜在性能问题：这里不需要自动去重，用UNION会额外执行去重操作，换成UNION ALL能提升查询效率，同时避免误删有效数据。

修正后的查询语句 #

-- 生成父记录：每个Parent对应一条汇总记录
SELECT 
    c1.PARENT_ID as ID,
    NULL as Parent_ID,
    c1.Child_ID as Child_1,
    c2.Child_ID as Child_2,
    c1.Identifier
FROM sampletable as c1
INNER JOIN sampletable as c2 
    ON c2.PARENT_ID = c1.PARENT_ID 
    AND c2.Identifier = 'OUT'
WHERE c1.Identifier = 'IN'

UNION ALL -- 用UNION ALL避免不必要的去重，提升效率

-- 生成子记录：直接输出每个子项，Child_1/Child_2设为NULL
SELECT 
    child_id as ID,
    Parent_id,
    NULL as Child_1,
    NULL as Child_2,
    Identifier
FROM sampletable 
WHERE parent_id IS NOT NULL
ORDER BY Parent_ID, ID; -- 可选排序，让结果和期望格式一致

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