基于多组关联关系的SQL计算技术问询

首先，我先假设你的数据库表结构大概是这样（如果和实际有出入，你可以调整字段名）：

-- 地点表
CREATE TABLE locations (
    location_id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 发票表
CREATE TABLE invoices (
    invoice_id INT PRIMARY KEY,
    location_id INT REFERENCES locations(location_id),
    status INT -- 1、2为状态值
);

-- 组表
CREATE TABLE groups (
    group_id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 地点-组多对多关联表
CREATE TABLE location_groups (
    location_id INT REFERENCES locations(location_id),
    group_id INT REFERENCES groups(group_id),
    PRIMARY KEY (location_id, group_id)
);

根据你的示例数据，先插入测试数据验证：

-- 插入地点
INSERT INTO locations VALUES (5, 'Location 5'), (7, 'Location 7');

-- 插入发票
INSERT INTO invoices VALUES 
(1,5,1), (2,5,1), (3,5,1), (4,5,2),
(5,7,1), (6,7,2), (7,7,2);

-- 插入组
INSERT INTO groups VALUES (1, 'Group 1'), (2, 'Group 2');

-- 插入多对多关联
INSERT INTO location_groups VALUES (5,1), (5,2), (7,2);

1. 创建视图实现组级汇总 #

视图是最直观的方式，适合频繁查询的场景：

CREATE VIEW group_invoice_summary AS
SELECT
    g.group_id,
    g.name AS group_name,
    SUM(CASE WHEN i.status = 1 THEN 1 ELSE 0 END) AS status_1_count,
    SUM(CASE WHEN i.status = 2 THEN 1 ELSE 0 END) AS status_2_count,
    COUNT(i.invoice_id) AS total_invoices
FROM groups g
LEFT JOIN location_groups lg ON g.group_id = lg.group_id
LEFT JOIN locations l ON lg.location_id = l.location_id
LEFT JOIN invoices i ON l.location_id = i.location_id
GROUP BY g.group_id, g.name
ORDER BY g.group_id;

查询这个视图就能得到结果：

SELECT * FROM group_invoice_summary;

结果会是：

这里Group 2统计了Location5（3+1）和Location7（1+2）的所有发票，完全符合你的需求。

2. 创建函数支持动态检索 #

如果需要支持动态筛选（比如按组ID、状态过滤），可以写一个函数：

CREATE OR REPLACE FUNCTION get_group_invoice_summary(p_group_id INT DEFAULT NULL)
RETURNS TABLE (
    group_id INT,
    group_name VARCHAR(50),
    status_1_count INT,
    status_2_count INT,
    total_invoices INT
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        g.group_id,
        g.name AS group_name,
        SUM(CASE WHEN i.status = 1 THEN 1 ELSE 0 END) AS status_1_count,
        SUM(CASE WHEN i.status = 2 THEN 1 ELSE 0 END) AS status_2_count,
        COUNT(i.invoice_id) AS total_invoices
    FROM groups g
    LEFT JOIN location_groups lg ON g.group_id = lg.group_id
    LEFT JOIN locations l ON lg.location_id = l.location_id
    LEFT JOIN invoices i ON l.location_id = i.location_id
    WHERE p_group_id IS NULL OR g.group_id = p_group_id
    GROUP BY g.group_id, g.name
    ORDER BY g.group_id;
END;
$$ LANGUAGE plpgsql;

使用方式：

• 查询所有组：SELECT * FROM get_group_invoice_summary();
• 查询指定组（比如Group 2）：SELECT * FROM get_group_invoice_summary(2);

关键逻辑说明 #

• 多对多关联处理：通过location_groups关联表将组和地点绑定，确保一个地点属于多个组时，其发票数会被每个关联组分别统计，不会遗漏。
• 条件计数：用CASE WHEN实现按状态分类计数，比单独写子查询更高效，逻辑也更清晰。
• LEFT JOIN：确保即使某个组没有关联任何地点或发票，也会返回0值的计数，避免遗漏组本身的数据。

如果你的数据库是MySQL，语法略有不同（比如函数需要用DELIMITER分隔，视图逻辑基本一致），可以根据实际情况调整。

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