如何通过三表JOIN查询计算用户总库存价值并取前5名结果

实现方案 #

你可以通过一次多表关联+聚合查询完成需求，完全替换原有嵌套循环逻辑，同时实现库存总价值计算、排序、取Top5的要求，相比原方案性能提升非常明显。

核心SQL逻辑

你需要关联三张表做分组聚合计算，SQL语句如下：

SELECT 
  u.userID,
  u.username,
  IFNULL(SUM(i.value * inv.quantity), 0) AS total_inventory_value
FROM users u
LEFT JOIN inventories inv ON u.userID = inv.userID
LEFT JOIN items i ON inv.itemID = i.itemID
WHERE u.accountVerified = 'true'
GROUP BY u.userID, u.username
ORDER BY total_inventory_value DESC
LIMIT 5

逻辑说明：

• 首先从用户表筛选出所有已验证账号的用户
• 左连库存表，匹配对应用户的所有道具库存记录
• 再左连道具表，匹配每个库存道具对应的价值
• 按用户ID分组，计算每个用户所有道具的总价值（道具单价 * 持有数量求和），无库存的用户总价值默认记为0
• 按总价值降序排序，直接取前5条结果

优化后完整代码

替换原有嵌套查询的全部逻辑即可，同时修正了原代码中HTML表格标签顺序错误的问题：

echo "<table>
<tr>
<th>Inventory Value</th>
<th>Username</th>
</tr>";

// 一次查询直接获取库存价值Top5的用户
$topUsersResult = $db->query("
SELECT 
  u.userID,
  u.username,
  IFNULL(SUM(i.value * inv.quantity), 0) AS total_inventory_value
FROM users u
LEFT JOIN inventories inv ON u.userID = inv.userID
LEFT JOIN items i ON inv.itemID = i.itemID
WHERE u.accountVerified = 'true'
GROUP BY u.userID, u.username
ORDER BY total_inventory_value DESC
LIMIT 5
");

while ($user = $db->fetch_row($topUsersResult)) {
  echo "<tr>";
  echo "<td>".money_format($user['total_inventory_value'])."</td>";
  // 如果你原来的username()函数有自定义逻辑，保留原写法即可：echo "<td>".username($user['userID'])."</td>";
  echo "<td>".$user['username']."</td>";
  echo "</tr>";
}

echo "</table>";

如果你的用户表中用户名字段命名不是username，或者username()函数有特殊的业务处理逻辑，把用户名输出部分调整为你原有逻辑即可。

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