PostgreSQL中如何合并两个SELECT查询以获取店铺聚合统计数据

问题修正说明 #

你提供的原始SQL存在两处笔误：

1. 第一个查询中COUNT("Fins_shop"."name") as count_goods后缺少逗号，且MAX("Fins_price"."price")表名错误，商品价格存储在item表中，对应字段为Fins_item.price
2. 第二个查询中SUM("Fins_department"."staff_amount")后多了冗余逗号

合并查询方案 #

合并两个查询的核心注意点：直接关联shop、department、item三张表时，一个部门对应多个商品会导致部门记录被重复展开，直接统计部门数量会得到错误结果，需通过DISTINCT去重或CTE分表聚合解决。

方案1：单查询去重聚合（性能最优，适合大多数场景）

SELECT 
    "Fins_shop"."name",
    COUNT("Fins_item"."id") AS count_goods,
    MAX("Fins_item"."price") AS max_price,
    COUNT(DISTINCT "Fins_department"."id") AS count_department,
    SUM(DISTINCT "Fins_department"."staff_amount") AS total_staff
FROM "Fins_shop" 
INNER JOIN "Fins_department" 
ON "Fins_department"."shop_id" = "Fins_shop"."id"
LEFT JOIN "Fins_item" 
ON "Fins_item"."department_id" = "Fins_department"."id"
GROUP BY "Fins_shop"."name";

说明：此处用LEFT JOIN关联item表，可保留没有商品的部门统计结果，如果不需要可改回INNER JOIN

方案2：CTE分聚合后关联（结果最准确，适合有部门无商品的场景）

WITH shop_goods AS (
    SELECT 
        "Fins_shop"."id" AS shop_id,
        "Fins_shop"."name",
        COUNT("Fins_item"."id") AS count_goods,
        MAX("Fins_item"."price") AS max_price
    FROM "Fins_shop" 
    JOIN "Fins_department" ON "Fins_department"."shop_id" = "Fins_shop"."id"
    JOIN "Fins_item" ON "Fins_item"."department_id" = "Fins_department"."id"
    GROUP BY "Fins_shop"."id", "Fins_shop"."name"
),
shop_dept AS (
    SELECT 
        "Fins_shop"."id" AS shop_id,
        COUNT("Fins_department"."id") AS count_department,
        SUM("Fins_department"."staff_amount") AS total_staff
    FROM "Fins_shop" 
    JOIN "Fins_department" ON "Fins_department"."shop_id" = "Fins_shop"."id"
    GROUP BY "Fins_shop"."id"
)
SELECT 
    sg.name,
    COALESCE(sg.count_goods, 0) AS count_goods,
    COALESCE(sg.max_price, 0) AS max_price,
    sd.count_department,
    sd.total_staff
FROM shop_goods sg
JOIN shop_dept sd ON sg.shop_id = sd.shop_id;

Django ORM实现方案 #

不需要写原生SQL，直接用Django自带的聚合函数即可实现：

from django.db.models import Count, Max, Sum

# 统计结果
shop_stats = Shop.objects.annotate(
    count_goods=Count('department_filter__item_filter__id'),
    max_price=Max('department_filter__item_filter__price'),
    count_department=Count('department_filter__id', distinct=True),
    total_staff=Sum('department_filter__staff_amount', distinct=True)
).values('name', 'count_goods', 'max_price', 'count_department', 'total_staff')

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