能否基于transactions表的reference_table字段值关联对应数据表？

这种写法根本行不通。SQL是静态解析的，执行时没法把T.reference_table的字段值当作实际表名来关联——数据库会直接把T.reference_table当成一个不存在的表名，直接抛出语法错误。

正确实现方案 #

方案1：用UNION ALL拼接结果（表数量固定时用）

如果只有online_recharges和wallet_deductions这两张关联表，直接分别关联后用UNION ALL合并结果就行：

SELECT 
  T.reference_table,
  ref.remarks,
  ref.amount,
  T.created_at
FROM transactions AS T
LEFT JOIN online_recharges AS ref 
  ON ref.online_recharge_id = T.reference_id 
  AND T.reference_table = 'online_recharges'
UNION ALL
SELECT 
  T.reference_table,
  ref.remarks,
  ref.amount,
  T.created_at
FROM transactions AS T
LEFT JOIN wallet_deductions AS ref 
  ON ref.wallet_deduction_id = T.reference_id 
  AND T.reference_table = 'wallet_deductions'
ORDER BY created_at DESC;

注意：你提供的wallet_deductions表主键字段写的是wllet_deduction_id，应该是笔误，实际要改成wallet_deduction_id才能关联成功。另外，每个子查询返回的字段数量、类型必须完全一致，UNION ALL才能正常工作。

方案2：动态SQL（表数量不固定时用）

如果后续可能新增更多关联表，静态SQL维护起来太麻烦，可以用数据库支持的动态SQL语法。比如MySQL中的预处理语句写法：

SET @sql = CONCAT(
  'SELECT T.reference_table, ref.remarks, ref.amount, T.created_at FROM transactions AS T ',
  (SELECT GROUP_CONCAT(
    'LEFT JOIN ', table_name, ' AS ref ON ref.', reference_field, ' = T.reference_id AND T.reference_table = ''', table_name, ''''
  ) FROM (
    SELECT 'online_recharges' AS table_name, 'online_recharge_id' AS reference_field
    UNION ALL
    SELECT 'wallet_deductions' AS table_name, 'wallet_deduction_id' AS reference_field
  ) AS tables),
  ' ORDER BY T.created_at DESC'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

动态SQL会根据你定义的表列表自动生成关联语句，扩展性更好，但要注意防范SQL注入风险，确保用到的表名和字段名都是可信的。

内容的提问来源于stack exchange，提问作者Mr.Singh