SQLite左连接查询计数结果异常问题求助

看起来你的查询遇到了两个关键问题，导致计数结果不符合预期：

问题分析 #

1. 笛卡尔积导致重复计数：当你同时对elemento_lista_spesa和utente_lista_spesa做左连接时，如果一个购物清单（lista_spesa）有多个配料和多个用户，两个表的关联会产生笛卡尔积——每个配料会和每个用户配对，这会让count()统计到重复的行，导致结果被放大。
2. 分组字段错误：你现在按b.id_lista_spesa和c.id_lista_spesa分组，但这两个字段在左连接时可能为NULL（当某个清单没有配料或用户时），而且本质上你需要按每个购物清单本身来分组，应该用a.id_interno作为分组依据。

修正后的查询方案 #

方案1：使用子查询分别统计（推荐，性能更优） #

先分别统计每个清单的配料数和用户数，再关联回主表，避免笛卡尔积问题：

SELECT
    a.id_interno,
    a.id_esterno,
    COALESCE(b.ingrediente_count, 0) AS ingrediente_count,
    COALESCE(c.utente_count, 0) AS utente_count
FROM lista_spesa a
LEFT JOIN (
    SELECT id_lista_spesa, COUNT(id_ingrediente) AS ingrediente_count
    FROM elemento_lista_spesa
    GROUP BY id_lista_spesa
) b ON b.id_lista_spesa = a.id_interno
LEFT JOIN (
    SELECT id_lista_spesa, COUNT(id_utente) AS utente_count
    FROM utente_lista_spesa
    GROUP BY id_lista_spesa
) c ON c.id_lista_spesa = a.id_interno;

这里用COALESCE()是为了把没有配料/用户的清单的计数显示为0，而不是NULL。

方案2：使用COUNT(DISTINCT)修正重复计数 #

如果你不想用子查询，也可以通过统计唯一ID来避免重复计数，但这种方式在数据量大时性能不如子查询：

SELECT
    a.id_interno,
    a.id_esterno,
    COUNT(DISTINCT b.id_ingrediente) AS ingrediente_count,
    COUNT(DISTINCT c.id_utente) AS utente_count
FROM lista_spesa a
LEFT JOIN elemento_lista_spesa b ON b.id_lista_spesa = a.id_interno
LEFT JOIN utente_lista_spesa c ON c.id_lista_spesa = a.id_interno
GROUP BY a.id_interno, a.id_esterno;

注意这里分组字段改成了a.id_interno和a.id_esterno（因为id_interno是主键，分组时带上id_esterno也没问题，或者只按a.id_interno分组也可以，取决于SQLite的模式）。

这两种方案都能解决你遇到的计数异常问题，推荐优先用子查询的方式，逻辑更清晰且性能更好。

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