SQL通过JOIN+GROUP BY统计结果更新app表应用关联图表数字段

你编写的子查询未和外层待更新的app表做关联，执行后会返回所有app对应的图表统计值，是一个多行结果集，而update语句中set后的等号要求右侧只能返回单个值，因此触发了报错。

方案1：修正关联子查询 #

给内层子查询增加与外层app表的关联条件，确保每一行app数据对应的子查询仅返回当前app的统计值：

UPDATE app
SET number_chart = (
    SELECT COUNT(*)
    FROM chart
    WHERE chart.ID_APP = app.ID_App
);

该写法逻辑简单，适合数据量不大的场景，没有关联图表的应用会自动统计为0。

方案2：关联更新（性能更优） #

先预统计每个应用的图表数量，再通过关联查询批量更新，适合数据量较大的场景，不同数据库写法略有差异：

MySQL 写法 #

UPDATE app
LEFT JOIN (
    SELECT ID_APP, COUNT(*) AS chart_cnt
    FROM chart
    GROUP BY ID_APP
) AS stats ON app.ID_App = stats.ID_APP
SET app.number_chart = COALESCE(stats.chart_cnt, 0);

SQL Server 写法 #

UPDATE a
SET a.number_chart = COALESCE(s.chart_cnt, 0)
FROM app a
LEFT JOIN (
    SELECT ID_APP, COUNT(*) AS chart_cnt
    FROM chart
    GROUP BY ID_APP
) s ON a.ID_App = s.ID_APP;

这里使用LEFT JOIN是为了覆盖没有关联任何图表的应用，COALESCE函数会把统计得到的空值转为0，符合统计逻辑。

可选替代方案：使用视图代替物理存储 #

如果该统计值不需要持久化存储，仅在查询时使用，可以直接创建视图，避免后续chart表数据变动后还要同步维护app表的number_chart字段：

CREATE VIEW app_chart_count_view AS
SELECT 
    a.ID_App,
    a.`app name`,
    COALESCE(COUNT(c.ID_chart), 0) AS number_of_chart
FROM app a
LEFT JOIN chart c ON a.ID_App = c.ID_APP
GROUP BY a.ID_App, a.`app name`;

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