MySQL分组查询位置时汇总资产数量的SQL语句报错解决

错误原因 #

你遇到的#1242 - Subquery returns more than 1 row错误，是因为SELECT列表中的子查询未做聚合处理：当一个位置对应多条asset_location_rel记录时，子查询会返回多个units值，而SQL要求SELECT里的标量子查询必须仅返回单个值。

正确SQL写法 #

方法1：修正子查询（聚合后返回单值） #

在子查询中使用SUM()函数汇总该位置的资产总数，同时处理无资产的情况（返回0）：

SELECT
  locations.location_id AS ID,
  locations.location_name AS Lokation,
  locations.row_column AS `Row/Column`,
  COALESCE(
    (SELECT SUM(units)
     FROM asset_location_rel
     WHERE asset_location_rel.location_id = locations.location_id),
    0
  ) AS NumItems
FROM locations
WHERE locations.location_deleted_datetime = '0000-00-00 00:00:00'
  AND locations.location_id = 557 -- 测试位置过滤
ORDER BY locations.location_id DESC;

方法2：LEFT JOIN结合GROUP BY（更高效） #

通过左连接后分组聚合，避免子查询的性能问题：

SELECT
  l.location_id AS ID,
  l.location_name AS Lokation,
  l.row_column AS `Row/Column`,
  COALESCE(SUM(alr.units), 0) AS NumItems
FROM locations l
LEFT JOIN asset_location_rel alr ON alr.location_id = l.location_id
WHERE l.location_deleted_datetime = '0000-00-00 00:00:00'
  AND l.location_id = 557 -- 测试位置过滤
GROUP BY l.location_id, l.location_name, l.row_column
ORDER BY l.location_id DESC;

说明 #

• COALESCE()函数用于处理无关联资产的位置，确保返回0而非NULL
• 需根据locations表的实际字段名调整location_name、row_column，确保和你期望的输出列对应
• 若要查询所有未删除位置，只需去掉AND locations.location_id = 557条件

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