如何编写SQL关联三张表分组统计用户的车辆、船只拥有数量？

现有3张业务表结构及样例数据如下：

• ApplicationUser（用户基础信息表）
  • 核心字段：UserID（用户唯一ID）、UserName（用户名）
  • 样例数据：UserID=1对应用户名user1，UserID=2对应用户名user2
• UserShip（用户船只关联表）
  • 核心字段：UserShipID（关联记录ID）、UserID（关联用户ID）、ShipID（关联船只ID）
  • 样例数据：UserID=1关联ShipID=1，UserID=2关联ShipID=1
• UserCar（用户车辆关联表）
  • 核心字段：UserCarID（关联记录ID）、UserID（关联用户ID）、CarID（关联车辆ID）
  • 样例数据：UserID=1关联CarID=1，UserID=2关联CarID=2

需输出结果集包含三个字段：用户名、对应用户绑定的车辆总数、对应用户绑定的船只总数，参考预期结果：user1的车辆计数为1、船只计数为1。

直接将三张表做LEFT JOIN后直接分组COUNT，会因为车辆、船只两个关联表相对于用户表都是一对多关系，两表关联后产生笛卡尔积重复行，最终统计出的数量远大于实际值。

采用先分表聚合、再主表关联的逻辑，从根源避免笛卡尔积导致的计数错误：

SELECT
  au.UserName,
  -- 无关联数据时返回0而非null
  COALESCE(uc.car_total, 0) AS car_count,
  COALESCE(us.ship_total, 0) AS ship_count
FROM ApplicationUser au
-- 关联预聚合的车辆统计结果
LEFT JOIN (
  SELECT UserID, COUNT(CarID) AS car_total
  FROM UserCar
  GROUP BY UserID
) uc ON au.UserID = uc.UserID
-- 关联预聚合的船只统计结果
LEFT JOIN (
  SELECT UserID, COUNT(ShipID) AS ship_total
  FROM UserShip
  GROUP BY UserID
) us ON au.UserID = us.UserID

注：COALESCE是标准SQL函数，兼容MySQL、SQL Server、PostgreSQL等绝大多数数据库，无需额外替换函数名。

内容的提问来源于stack exchange，提问作者Cagatay Aydın