多表关联并按多列group by查询三表数据，生成指定格式结果

针对你提出的多表关联、按pr_id分组并生成指定格式统计结果的需求，我提供两种适配不同数据库的实现方案，确保能得到你期望的输出：

方案1：适配MySQL、PostgreSQL等无原生PIVOT的数据库 #

这类数据库可以通过CASE WHEN配合聚合函数来实现行转列需求，SQL代码如下：

SELECT
    SUM(t2.qty) AS total_qty,
    SUM(CASE WHEN t3.l_id = 1 THEN t2.qty ELSE 0 END) AS `1(l_id)`,
    SUM(CASE WHEN t3.l_id = 2 THEN t2.qty ELSE 0 END) AS `2(l_id)`,
    t1.pr_id
FROM
    表1 t1
JOIN 表2 t2 ON t1.pr_id = t2.pr_id
JOIN 表3 t3 ON t1.c_id = t3.c_id
GROUP BY
    t1.pr_id
ORDER BY
    t1.pr_id;

代码说明： #

• 表关联：通过JOIN将三张表关联起来——表1和表2通过pr_id关联，表1和表3通过c_id关联，把数量数据qty和对应的l_id映射关系打通。
• 聚合统计：
  • SUM(t2.qty)直接计算每个pr_id的总数量total_qty；
  • CASE WHEN判断l_id的值，分别累加对应l_id下的qty，没有匹配记录时返回0，保证结果列不会出现NULL。
• 分组排序：按pr_id分组得到每个产品的统计结果，最后按pr_id排序让结果更规整。

方案2：适配SQL Server、Oracle等支持PIVOT的数据库 #

如果你的数据库支持PIVOT函数，可以用更简洁的方式实现行转列，代码如下：

WITH CombinedData AS (
    -- 先整合三张表的关联数据
    SELECT
        t2.qty,
        t3.l_id,
        t1.pr_id
    FROM
        表1 t1
    JOIN 表2 t2 ON t1.pr_id = t2.pr_id
    JOIN 表3 t3 ON t1.c_id = t3.c_id
)
SELECT
    -- 计算每个pr_id的总数量
    SUM(qty) OVER (PARTITION BY pr_id) AS total_qty,
    -- 用COALESCE把NULL转为0，符合需求格式
    COALESCE([1], 0) AS `1(l_id)`,
    COALESCE([2], 0) AS `2(l_id)`,
    pr_id
FROM
    CombinedData
PIVOT (
    -- 按l_id列转置，统计对应qty的总和
    SUM(qty)
    FOR l_id IN ([1], [2])
) AS PivotTable
ORDER BY
    pr_id;

代码说明： #

• CTE整合数据：先用公共表表达式（CTE）把三张表的关联结果整合为包含qty、l_id、pr_id的基础数据集，让后续逻辑更清晰。
• PIVOT行转列：通过PIVOT函数把l_id的行值转为列名，并统计每个l_id对应的qty总和。
• 处理NULL值：用COALESCE把PIVOT生成的NULL值转为0，确保没有对应l_id数据时显示0。
• 总数量计算：用窗口函数SUM(qty) OVER (PARTITION BY pr_id)快速计算每个pr_id的总数量，避免重复聚合。

两种方案执行后都会得到你期望的统计结果：

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