如何按权重优先规则统计论文对应成果类型的数量？

核心思路 #

先为每篇论文（相同Paperti）筛选出权重最低的成果类型，再基于这个筛选结果完成两个统计需求：

1. 展示每篇论文对应的低权重成果类型及计数（每篇论文计数固定为1）
2. 统计各类低权重成果类型的总数量

第一个结果集：单篇论文的低权重成果统计 #

方法1：使用窗口函数（MySQL 8.0+支持）

WITH paper_min_weight AS (
    SELECT 
        p.Paperti,
        p.Tow,
        -- 按论文分组，每组内按权重升序排序，权重最小的记录行号为1
        ROW_NUMBER() OVER (PARTITION BY p.Paperti ORDER BY tw.weight ASC) AS rn
    FROM papers p
    JOIN typeofwwork tw ON p.Tow = tw.tow
)
SELECT 
    Paperti AS PaperTI,
    1 AS Count,
    Tow
FROM paper_min_weight
WHERE rn = 1;

方法2：使用子查询（兼容低版本MySQL）

SELECT 
    p.Paperti AS PaperTI,
    1 AS Count,
    p.Tow
FROM papers p
JOIN typeofwwork tw ON p.Tow = tw.tow
-- 筛选当前论文对应的最小权重记录
WHERE tw.weight = (
    SELECT MIN(tw2.weight)
    FROM papers p2
    JOIN typeofwwork tw2 ON p2.Tow = tw2.tow
    WHERE p2.Paperti = p.Paperti
);

执行后结果：

第二个结果集：各类低权重成果的总数量统计 #

方法1：基于窗口函数的CTE

WITH paper_min_weight AS (
    SELECT 
        p.Paperti,
        p.Tow,
        ROW_NUMBER() OVER (PARTITION BY p.Paperti ORDER BY tw.weight ASC) AS rn
    FROM papers p
    JOIN typeofwwork tw ON p.Tow = tw.tow
)
SELECT 
    COUNT(*) AS Count,
    Tow
FROM paper_min_weight
WHERE rn = 1
GROUP BY Tow;

方法2：基于子查询

SELECT 
    COUNT(*) AS Count,
    p.Tow
FROM papers p
JOIN typeofwwork tw ON p.Tow = tw.tow
WHERE tw.weight = (
    SELECT MIN(tw2.weight)
    FROM papers p2
    JOIN typeofwwork tw2 ON p2.Tow = tw2.tow
    WHERE p2.Paperti = p.Paperti
)
GROUP BY p.Tow;

执行后结果：

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