请求合并两段SQL查询语句，关联条件为m.id = i.item_id

根据需求，将两段SQL以**主表ID等于子项表的子项ID（m.id = i.item_id）**为关联条件合并，最终SQL如下：

SELECT 
    a.id, 
    a.total_amount, 
    b.debit, 
    b.credit
FROM (
    -- 获取符合条件的主表ID及总金额
    SELECT 
        m.id, 
        m.total_amount
    FROM table_main m
    INNER JOIN table_item i ON m.id = i.ref_id
    WHERE m.journal_id = 10 AND m.period = 24
    GROUP BY m.id
) a
LEFT JOIN (
    -- 统计子项对应的借贷金额
    SELECT 
        i.item_id, 
        SUM(i.debit_amount) AS debit, 
        SUM(i.credit_amount) AS credit
    FROM table_main m
    INNER JOIN table_item i ON m.id = i.ref_id
    WHERE i.item_id IN (
        SELECT id
        FROM table_main
        WHERE journal_id = 10 AND period = 24
    )
    AND m.journal_id IN (18, 20) 
    AND m.posted = '1' 
    AND i.gl_type IN ('chk', 'pmt')
    GROUP BY i.item_id
) b ON a.id = b.item_id
ORDER BY a.purchase_invoice_id DESC;

说明 #

• 子查询a保留第一段查询的核心结果，子查询b保留第二段的统计逻辑
• 使用LEFT JOIN关联，确保所有符合第一段条件的主表记录都能返回，无对应子项统计时debit和credit将显示为NULL
• 若仅需同时存在主表记录和子项统计的结果，可将LEFT JOIN替换为INNER JOIN

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