如何用单条SQL语句在PHP中同时查询存款与取款总金额

核心SQL语句 #

使用条件聚合实现单次表扫描返回两个统计结果，相比两条独立查询性能提升显著，适配大数据量场景：

SELECT
  SUM(CASE WHEN type = 'deposit' THEN amount ELSE 0 END) AS total_deposit,
  SUM(CASE WHEN type = 'withdraw' THEN amount ELSE 0 END) AS total_withdraw
FROM transactions
WHERE cId = 10

注：如果你的交易表中区分存/取款的字段名、字段枚举值和上述示例不同，直接修改CASE WHEN后的判断条件即可。

PHP侧接收赋值示例 #

以PDO连接为例：

// 数据库连接配置请替换为你实际的环境参数
$pdo = new PDO('mysql:host=127.0.0.1;dbname=你的库名;charset=utf8mb4', '数据库用户名', '数据库密码');
$stmt = $pdo->prepare("SELECT
  SUM(CASE WHEN type = 'deposit' THEN amount ELSE 0 END) AS total_deposit,
  SUM(CASE WHEN type = 'withdraw' THEN amount ELSE 0 END) AS total_withdraw
FROM transactions
WHERE cId = ?");
$stmt->execute([10]);
$res = $stmt->fetch(PDO::FETCH_ASSOC);

// 赋值到对应变量
$total_deposit = (int)$res['total_deposit']; // 对应示例预期值5500（存款总额）
$total_withdraw = (int)$res['total_withdraw']; // 对应示例预期值2500（取款总额）

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