多层级用户父账号余额查询SQL及PHP实现方案咨询

1 SQL查询写法 #

针对你的树形层级用户结构，最通用的实现方案是使用MySQL 8.0+/MariaDB 10.2+支持的递归公共表表达式（CTE），单条SQL即可拉取指定用户所有上级父账号及对应余额：

WITH RECURSIVE parent_hierarchy AS (
    -- 初始查询：获取目标用户的直接上级
    SELECT parentid AS userid FROM user WHERE userid = :target_userid
    UNION ALL
    -- 递归向上查询所有上级，直到顶级父账号（无匹配上级时自动停止）
    SELECT u.parentid AS userid 
    FROM user u
    INNER JOIN parent_hierarchy ph ON u.userid = ph.userid
)
-- 关联余额表获取所有上级的余额数据
SELECT ph.userid, ub.balance 
FROM parent_hierarchy ph
INNER JOIN `user Balance` ub ON ph.userid = ub.user;

如果使用不支持递归CTE的低版本MySQL，且你的用户层级固定为示例中的4级，也可以用多层自连接实现，但扩展性较差，更推荐升级数据库版本使用递归方案。

2 PHP实现建议 #

推荐使用PDO做参数化查询避免SQL注入，判断逻辑直接校验所有上级余额是否均大于等于交易金额即可，参考代码如下：

<?php
// 数据库配置
$dbHost = '127.0.0.1';
$dbName = '替换为你的数据库名';
$dbUser = '替换为数据库账号';
$dbPass = '替换为数据库密码';

// 交易参数
$targetUserId = 398933; // 发起交易的用户ID
$tradeAmount = 100; // 本次交易金额

try {
    $pdo = new PDO("mysql:host=$dbHost;dbname=$dbName;charset=utf8mb4", $dbUser, $dbPass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 执行递归查询
    $sql = <<<SQL
WITH RECURSIVE parent_hierarchy AS (
    SELECT parentid AS userid FROM user WHERE userid = :target_userid
    UNION ALL
    SELECT u.parentid AS userid 
    FROM user u
    INNER JOIN parent_hierarchy ph ON u.userid = ph.userid
)
SELECT ub.balance 
FROM parent_hierarchy ph
INNER JOIN `user Balance` ub ON ph.userid = ub.user;
SQL;
    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(':target_userid', $targetUserId, PDO::PARAM_INT);
    $stmt->execute();
    $parentBalances = $stmt->fetchAll(PDO::FETCH_COLUMN);

    // 校验所有上级余额是否充足
    $allowTrade = true;
    foreach ($parentBalances as $balance) {
        if ($balance < $tradeAmount) {
            $allowTrade = false;
            break;
        }
    }

    if ($allowTrade) {
        echo "交易通过";
        // 后续交易执行逻辑
    } else {
        echo "交易驳回，上级账号余额不足";
    }

} catch (PDOException $e) {
    die("数据库错误：" . $e->getMessage());
}
?>

如果需要定位具体是哪个上级余额不足，查询时保留userid字段，遍历过程中记录对应账号即可。

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