多表查询SQL技术问询：从三张表提取指定字段匹配预期结果

我手头有三张业务数据表，字段信息如下：

• tbl_savac_client：包含ac_no（账号）、member_name（会员姓名）、balance（账户余额）字段
• tbl_savac_posting：用于记录存款交易，字段包括ac_no、mr_no、date（交易日期）、installment（存款金额）、description（交易描述）
• tbl_withdraw_posting：用于记录取款交易，字段包括ac_no、date（交易日期）、description（交易描述）、wit_amnt（取款金额）、mr_no

我想要查询出指定账号在某个日期范围内的交易记录，结果需要包含客户的姓名、账户余额，以及对应的存款/取款明细（每条交易单独一行，区分存款和取款金额）。但我自己写的SQL跑出来的结果不符合预期，麻烦帮忙看看怎么调整？

我写的原查询语句如下：

SELECT sc.ac_no, sc.blance, sc.member_name, sp.date, wp.date, sp.installment, sp.description, sp.mr_no, wp.wit_amnt, wp.description, wp.mr_no FROM tbl_savac_client sc LEFT JOIN (SELECT ac_no, mr_no, date, installment, description FROM tbl_savac_posting) sp ON sc.ac_no = sp.ac_no LEFT JOIN (SELECT ac_no, date, description, wit_amnt, mr_no FROM tbl_withdraw_posting) wp ON sc.ac_no = wp.ac_no WHERE sc.ac_no = '$ac_no' AND sp.date BETWEEN '".$start_date."' AND '".$end_date."'

问题分析 #

原查询存在几个明显的问题：

1. 拼写错误：把balance写成了blance，会导致字段不存在的报错
2. 字段重名冲突：sp.date和wp.date同时出现在SELECT中，结果里会有两个同名的date字段，无法区分是存款还是取款日期
3. JOIN逻辑错误：同时LEFT JOIN两张交易表会产生笛卡尔积，导致一条存款记录和一条取款记录被错误合并成一行，不符合“每条交易单独展示”的预期
4. 过滤条件位置错误：WHERE子句中加入sp.date的范围条件，会把没有存款记录的客户/取款记录直接过滤掉，让LEFT JOIN失去原本的作用，变成了INNER JOIN的效果
5. 变量拼接错误：日期变量的写法'".$start_date."'是错误的，会导致SQL无法正确解析日期值

调整后的SQL语句 #

根据你的需求，应该把存款和取款记录分别查询后用UNION ALL合并，这样每条交易都会单独成一行，同时保留客户的基本信息：

SELECT 
    sc.member_name,
    sc.balance,
    sp.date AS transaction_date,
    sp.installment AS deposit_amount,
    NULL AS withdraw_amount,
    sp.description,
    sp.mr_no
FROM tbl_savac_client sc
LEFT JOIN tbl_savac_posting sp 
    ON sc.ac_no = sp.ac_no
    AND sp.date BETWEEN '$start_date' AND '$end_date'
WHERE sc.ac_no = '$ac_no'

UNION ALL

SELECT 
    sc.member_name,
    sc.balance,
    wp.date AS transaction_date,
    NULL AS deposit_amount,
    wp.wit_amnt AS withdraw_amount,
    wp.description,
    wp.mr_no
FROM tbl_savac_client sc
LEFT JOIN tbl_withdraw_posting wp 
    ON sc.ac_no = wp.ac_no
    AND wp.date BETWEEN '$start_date' AND '$end_date'
WHERE sc.ac_no = '$ac_no'

ORDER BY transaction_date;

调整说明 #

1. 修正了balance的拼写错误，确保字段能被正确识别
2. 给交易日期字段起了统一的别名transaction_date，避免重名混淆
3. 用UNION ALL分别处理存款和取款记录，确保每条交易单独一行，彻底解决笛卡尔积问题
4. 把日期范围条件移到JOIN的ON子句中，这样即使客户在该范围内没有存款/取款记录，也能保留客户的基本信息（如果不需要无交易记录的客户行，可以把日期条件移回WHERE子句）
5. 调整了变量的拼接方式，确保日期值能被SQL正确解析（另外提醒下：如果是PHP等开发环境，建议使用预处理语句来避免SQL注入风险）
6. 区分了存款金额和取款金额，不存在的用NULL填充，完全匹配预期结果的展示样式

内容的提问来源于stack exchange，提问作者Md. Farhad Hossain