请求编写SQL查询：从表A、表B生成Clark的指定结果表

可以通过UNION ALL合并两个表的目标数据，再追加总计行来实现需求：

WITH combined_data AS (
    -- 提取表A中Clark的记录，金额带"+"
    SELECT 
        a_date AS Date,
        CONCAT('+', amount) AS Amount,
        1 AS sort_order
    FROM tableA
    WHERE name = 'Clark'
    
    UNION ALL
    
    -- 提取表B中符合条件的Clark记录，金额带"-"
    SELECT 
        b_date AS Date,
        CONCAT('-', amount) AS Amount,
        1 AS sort_order
    FROM tableB
    WHERE name = 'Clark'
      AND status = 'Paid'
      AND type = 'Loan'
),
total_calculation AS (
    -- 计算总计金额
    SELECT 
        'Total' AS Date,
        CASE 
            WHEN SUM(CASE WHEN Amount LIKE '+%' THEN SUBSTR(Amount, 2) ELSE -SUBSTR(Amount, 2) END) >= 0 
            THEN CONCAT('+', SUM(CASE WHEN Amount LIKE '+%' THEN SUBSTR(Amount, 2) ELSE -SUBSTR(Amount, 2) END))
            ELSE CONCAT('-', ABS(SUM(CASE WHEN Amount LIKE '+%' THEN SUBSTR(Amount, 2) ELSE -SUBSTR(Amount, 2) END)))
        END AS Amount,
        2 AS sort_order
    FROM combined_data
)

-- 合并明细与总计并排序
SELECT Date, Amount
FROM combined_data
UNION ALL
SELECT Date, Amount
FROM total_calculation
ORDER BY sort_order, Date;

关键说明 #

• 用UNION ALL合并两个表的目标数据，避免去重丢失有效记录
• 新增sort_order字段，确保明细行在前、总计行在后
• 总计计算时通过CASE语句解析带符号的金额值，计算实际总和后再添加正确的正负符号
• 明细行按日期排序，保证结果顺序与目标表一致

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