基于pid和qid分组时计算SUM字段差值的SQL实现方案

问题背景 #

现有数据库表tbl结构及数据如下：

需要编写SQL，计算指定pid（11和12）在同一qid下的sum字段差值，目标结果如下：

解决方案 #

原给出的GROUP BY pid, qid框架无法实现需求——因为该分组会把每个pid+qid组合单独拆分，无法跨pid计算差值。以下提供两种可行实现方式：

方式一：自连接查询 #

通过将表与自身连接，匹配同一qid下的两个目标pid，直接计算差值：

SELECT
    t1.pid AS Id1,
    t2.pid AS Id2,
    t1.qid,
    t1.sum - t2.sum AS difference
FROM
    tbl t1
JOIN tbl t2 
    ON t1.qid = t2.qid 
    AND t1.pid = 11 
    AND t2.pid = 12
WHERE
    t1.sum - t2.sum = 500; -- 若需保留所有qid的差值，可去掉此过滤条件

方式二：条件聚合查询 #

通过按qid分组，用CASE WHEN提取目标pid的sum值后计算差值：

SELECT
    11 AS Id1,
    12 AS Id2,
    qid,
    MAX(CASE WHEN pid = 11 THEN sum END) - MAX(CASE WHEN pid = 12 THEN sum END) AS difference
FROM
    tbl
GROUP BY
    qid
HAVING
    MAX(CASE WHEN pid = 11 THEN sum END) - MAX(CASE WHEN pid = 12 THEN sum END) = 500; -- 按需保留过滤条件

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