如何不借助中间表实现各学校测量最多主体的SQL查询？

我做编码有段时间了，这还是第一次找不到答案不得不提问。现在我在处理一个包含学校、班级、花园、植物和植物测量（rilevazioni）的数据库，需要写查询找出每所学校里完成测量最多的人员或班级。

我最初的做法是创建中间表统计每所学校关联的人员和班级的测量次数，再从中筛选数据，但这种方法效率很低，属于初级实现。下面是我当前的代码：

CREATE TABLE tabella_intermedia 
AS
    SELECT 
        scuola.nome AS nome_scuola, scuola.provincia AS provincia_scuola, 
        scuola.codmec AS codicemeccanografico_scuola,
        rilevazioni_per_persona.effettua_codfiscale AS persona_in_questione, 
        rilevazioni_per_classe.nomeclasse AS classe_in_questione,
        rilevazioni_per_persona.count AS numero_rilevazioni_effettuato_dalla_persona,
        rilevazioni_per_classe.count AS numero_rilevazioni_effettuato_dalla_classe
    FROM 
        scuola 
    NATURAL JOIN 
        RifIniz AS ScuolaRif
    LEFT JOIN 
        (SELECT rilevazione.effettua_codfiscale, COUNT(*) AS count 
         FROM rilevazione 
         GROUP BY rilevazione.effettua_codfiscale) AS rilevazioni_per_persona ON scuolaRif.codfiscale = rilevazioni_per_persona.effettua_codfiscale
    LEFT JOIN 
        (SELECT classe.codmec, classe.nomeclasse, COUNT(*) AS count
         FROM rilevazione 
         JOIN classe ON rilevazione.effettua_codmec = classe.codmec 
                     AND rilevazione.effettua_nome = classe.nomeclasse
         GROUP BY classe.nomeclasse, classe.codmec) AS rilevazioni_per_classe ON scuola.codmec = rilevazioni_per_classe.codmec
    GROUP BY 
        scuola.nome, scuola.codmec, 
        numero_rilevazioni_effettuato_dalla_persona, 
        numero_rilevazioni_effettuato_dalla_classe, 
        persona_in_questione, classe_in_questione;


SELECT DISTINCT ON (codicemeccanografico_scuola)
    nome_scuola,
    provincia_scuola,
    codicemeccanografico_scuola,
    CASE 
        WHEN COALESCE(numero_rilevazioni_effettuato_dalla_persona, 0) >= COALESCE(numero_rilevazioni_effettuato_dalla_classe, 0) /*COALESCE ci serve per trattare i valori nulli come zeri, invece che come sconosciuti, perché altrimenti ogni confronto tra un numero e un NULL darebbe NULL, e ciò non ci va bene*/
            THEN persona_in_questione /*in caso l'entità che ha effettuato più rilevazioni per la data scuola sia una persona inseriremo il suo codicefiscale qua dentro */
        ELSE classe_in_questione /*in caso tale entità sia una classe inseriremo il nome della classe*/
    END AS enità_che_ha_effettuato_più_rilevazioni,
    greatest(numero_rilevazioni_effettuato_dalla_classe, 
    numero_rilevazioni_effettuato_dalla_persona) AS numero_rilevazioni
FROM 
    tabella_intermedia
ORDER BY 
    codicemeccanografico_scuola, 
    GREATEST(numero_rilevazioni_effettuato_dalla_classe, numero_rilevazioni_effettuato_dalla_persona) DESC;

我试过网上各种方法都得不到预期结果，数据库里有14所学校，只有上面这个初级方法能返回14条对应记录，每条是学校里测量最多的班级名称或人员税号。现在需要一个不需要中间表的实现方案。

可以通过CTE（公共表表达式）整合统计逻辑，结合窗口函数直接筛选每所学校的最高记录，无需创建物理中间表。具体代码如下：

WITH rilevazioni_aggregate AS (
    -- 统计每个人员的测量次数，并关联到所属学校
    SELECT 
        s.nome AS nome_scuola,
        s.provincia AS provincia_scuola,
        s.codmec AS codicemeccanografico_scuola,
        r.effettua_codfiscale AS entità_id,
        COUNT(*) AS numero_rilevazioni
    FROM scuola s
    JOIN RifIniz sr ON s.codmec = sr.codmec
    JOIN rilevazione r ON sr.codfiscale = r.effettua_codfiscale
    GROUP BY s.nome, s.provincia, s.codmec, r.effettua_codfiscale
    
    UNION ALL
    
    -- 统计每个班级的测量次数，并关联到所属学校
    SELECT 
        s.nome AS nome_scuola,
        s.provincia AS provincia_scuola,
        s.codmec AS codicemeccanografico_scuola,
        c.nomeclasse AS entità_id,
        COUNT(*) AS numero_rilevazioni
    FROM scuola s
    JOIN classe c ON s.codmec = c.codmec
    JOIN rilevazione r ON c.codmec = r.effettua_codmec AND c.nomeclasse = r.effettua_nome
    GROUP BY s.nome, s.provincia, s.codmec, c.nomeclasse
    
    UNION ALL
    
    -- 补充无测量记录的学校，确保返回全部14条学校记录
    SELECT 
        nome AS nome_scuola,
        provincia AS provincia_scuola,
        codmec AS codicemeccanografico_scuola,
        NULL AS entità_id,
        0 AS numero_rilevazioni
    FROM scuola s
    WHERE NOT EXISTS (
        SELECT 1 FROM RifIniz sr WHERE sr.codmec = s.codmec
        UNION ALL
        SELECT 1 FROM classe c WHERE c.codmec = s.codmec
    )
),
ranked_rilevazioni AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY codicemeccanografico_scuola 
            ORDER BY numero_rilevazioni DESC
        ) AS rn
    FROM rilevazioni_aggregate
)
SELECT 
    nome_scuola,
    provincia_scuola,
    codicemeccanografico_scuola,
    entità_id AS enità_che_ha_effettuato_più_rilevazioni,
    numero_rilevazioni
FROM ranked_rilevazioni
WHERE rn = 1;

优化说明 #

1. 用CTE替代物理中间表：通过WITH子句创建临时聚合结果，避免磁盘存储开销，提升查询效率。
2. 统一统计结构：用UNION ALL将人员和班级的统计结果合并为同结构数据集，简化后续排序逻辑。
3. 窗口函数精准筛选：利用ROW_NUMBER()按学校分组，按测量次数降序排序，直接取每组第一条（即测量最多的记录）。
4. 兼容边界情况：补充无任何测量记录的学校数据，确保返回全部14所学校的结果。
5. 显式关联表字段：替换NATURAL JOIN为显式字段关联，避免因字段名隐式匹配导致的意外错误。

内容的提问来源于stack exchange，提问作者Iosif Petru Puha