如何避免SELECT子句中使用子查询实现文档金额统计？

表结构与测试数据 #

CREATE TABLE document_header 
(
    id int, 
    originalAmount decimal(5, 2), 
    doc_type char(2)
)

INSERT INTO document_header(id, originalAmount, doc_type) 
VALUES (1001, '200.00', 'PV'),
       (1002, '150.00', 'PV'),
       (1003, '300.00', 'IV'),
       (1004, '400.00', 'IV'),
       (1005, '600.00', 'IV')

CREATE TABLE document_allocation 
(
    id int, 
    allocatedto int, 
    allocatedfrom int, 
    allocatedamount decimal(5, 2)
)

INSERT INTO document_allocation 
VALUES (1, 1003, 1001, '100.00'),
       (2, 1004, 1001, '50.00'),
       (3, 1003, 1002, '50.00'),
       (4, 1005, 1001, '50.00'),
       (5, 1004, 1002, '50.00'),
       (6, 1003, 1001, '20.00')

预期结果 #

现有问题 #

现有带SELECT子查询的SQL可得到预期结果，但需要避免SELECT子句中的子查询：

SELECT
    originalDocument.id AS OriginalDocumentID,
    document_header.id AS allocatedBy,
    (SELECT SUM(document_header_a.originalAmount) 
     FROM document_header document_header_a 
     WHERE document_header_a.id = originalDocument.id),
    SUM(document_allocation.allocatedamount) AS amountAllocated
FROM
    document_header,
    document_allocation,
    document_header originalDocument
WHERE
    document_header.id = document_allocation.allocatedfrom AND
    originalDocument.id = document_allocation.allocatedto
GROUP BY
    originalDocument.id,
    document_header.id

尝试直接聚合原始金额时，出现金额翻倍问题（如1003对应1001的documentOriginalAmount变成600.00）：

SELECT
    originalDocument.id AS OriginalDocumentID,
    document_header.id AS allocatedBy,
    SUM(originalDocument.originalAmount) AS documentOriginalAmount,
    SUM(document_allocation.allocatedamount) AS amountAllocated
FROM
    document_header,
    document_allocation,
    document_header originalDocument
WHERE
    document_header.id = document_allocation.allocatedfrom AND
    originalDocument.id = document_allocation.allocatedto
GROUP BY
    originalDocument.id,
    document_header.id

错误执行结果：

解决方案 #

方法1：先聚合分配表，再关联表头表 #

先对document_allocation按目标文档(allocatedto)和来源文档(allocatedfrom)分组求和，再关联document_header获取原始金额，避免重复计算：

SELECT
    da.allocatedto AS originalDocumentId,
    dh.originalAmount AS documentOriginalAmount,
    da.allocatedfrom AS allocatedBy,
    da.total_allocated AS allocatedamount
FROM (
    SELECT 
        allocatedto,
        allocatedfrom,
        SUM(allocatedamount) AS total_allocated
    FROM document_allocation
    GROUP BY allocatedto, allocatedfrom
) da
JOIN document_header dh ON dh.id = da.allocatedto
ORDER BY da.allocatedto, da.allocatedfrom;

方法2：用非SUM聚合函数获取原始金额 #

由于每个文档的originalAmount是唯一值，分组后用MAX()/MIN()/AVG()等聚合函数代替SUM()，即可避免重复累加：

SELECT
    originalDocument.id AS originalDocumentId,
    document_header.id AS allocatedBy,
    MAX(originalDocument.originalAmount) AS documentOriginalAmount,
    SUM(document_allocation.allocatedamount) AS allocatedamount
FROM
    document_header
JOIN document_allocation ON document_header.id = document_allocation.allocatedfrom
JOIN document_header originalDocument ON originalDocument.id = document_allocation.allocatedto
GROUP BY
    originalDocument.id,
    document_header.id
ORDER BY originalDocument.id, allocatedBy;

两种方法均无需在SELECT子句中使用子查询，且能得到符合预期的结果。

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