如何在GROUP BY场景下仅执行一次子查询（Dynamics 365 SQL Server）

问题说明 #

关联invoices表后，左连接固定值子查询时，子查询结果会随发票行数重复，导致SUM计算结果为固定值乘以发票数量。复现代码如下：

CREATE TABLE Persons 
(
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

CREATE TABLE invoices 
(
    PersonID int,
    InvoiceId int,
);

INSERT INTO Persons (PersonID, LastName, FirstName) 
VALUES (1, 'test', 'test')

INSERT INTO invoices (PersonID, InvoiceId) VALUES (1, 1)
INSERT INTO invoices (PersonID, InvoiceId) VALUES (1, 2)
INSERT INTO invoices (PersonID, InvoiceId) VALUES (1, 3)
INSERT INTO invoices (PersonID, InvoiceId) VALUES (1, 4)
INSERT INTO invoices (PersonID, InvoiceId) VALUES (1, 5)

-- 结果为5（正确）
SELECT
    p.PersonID,
    SUM(s1.number)
FROM
    Persons p
LEFT OUTER JOIN
    (SELECT 5 AS number) AS s1 ON number > 1
GROUP BY 
    p.PersonID

-- 结果为25（错误，5*5，因子查询随发票行重复）
SELECT
    p.PersonID ,
    SUM(s1.number)
FROM
    Persons p
INNER JOIN
    invoices i ON i.PersonID = p.PersonID
LEFT OUTER JOIN
    (SELECT 5 AS number) AS s1 ON number > 1
GROUP BY 
    p.PersonID

解决方案（无需临时表） #

方案1：先聚合关联表，再连接固定值子查询 #

先对Persons和invoices的关联结果按PersonID分组，再与固定值子查询连接，确保子查询仅匹配一次：

SELECT
    p_grouped.PersonID,
    SUM(s1.number)
FROM
    (
        SELECT p.PersonID 
        FROM Persons p
        INNER JOIN invoices i ON i.PersonID = p.PersonID
        GROUP BY p.PersonID
    ) AS p_grouped
LEFT OUTER JOIN
    (SELECT 5 AS number) AS s1 ON s1.number > 1
GROUP BY 
    p_grouped.PersonID

方案2：聚合发票表后再关联所有表 #

如果需要保留发票的聚合统计（如发票数量），先对invoices按PersonID聚合，再关联Persons和固定值子查询：

SELECT
    p.PersonID,
    inv.InvoiceCount,
    COALESCE(s1.number, 0) AS FixedValue -- 或用SUM(s1.number)，结果一致
FROM
    Persons p
INNER JOIN
    (
        SELECT PersonID, COUNT(*) AS InvoiceCount 
        FROM invoices 
        GROUP BY PersonID
    ) AS inv ON inv.PersonID = p.PersonID
LEFT OUTER JOIN
    (SELECT 5 AS number) AS s1 ON s1.number > 1
GROUP BY 
    p.PersonID, inv.InvoiceCount, s1.number

方案3：使用APPLY并通过MAX/MIN替代SUM #

利用OUTER APPLY关联固定值子查询，由于每个分组内子查询结果一致，用MAX或MIN代替SUM避免重复累加：

SELECT
    p.PersonID,
    MAX(s1.number) -- MIN(s1.number) 结果相同
FROM
    Persons p
INNER JOIN
    invoices i ON i.PersonID = p.PersonID
OUTER APPLY
    (SELECT 5 AS number WHERE 5 > 1) AS s1
GROUP BY 
    p.PersonID

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