SQL Server How to execute subquery once based on the group by

mwngjboj 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(97)

I have a large query in a Dynamics 365 SQL Server database where I have an issue.

I try to reproduce the issue in a small query here:

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)

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

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

Running the query I get this result:

So if I make also a inner join to invoices it's will going to make the sub query to left outer join (select 5 as number) as s1 times the amount of invoices, and later by grouping by PersonId and making a sum on s1.number it's will be sum(s1.number) * the amount of invoices, if someone can help me how to avoid this, and if I make a inner join and sub query and make a group by, it's should only take the result from the sub query once based on the group by

I cannot use any temporary table solution because it's restricted in Dynamics

sql-server

来源：https://stackoverflow.com/questions/77144656/how-to-execute-subquery-once-based-on-the-group-by

1条答案

按热度按时间

mgdq6dx11#

Do the invoices based calculation in a subquery which groups by PersonID, then join that subquery:

SELECT
    p.PersonID ,
    SUM(s1.number)
FROM     Persons p
INNER JOIN     invoices i ON i.PersonID = p.PersonID
CROSS JOIN (SELECT 5 AS number) AS s1
GROUP BY 
    p.PersonID

PersonID	(No column name)
1	25

SELECT
    p.PersonID ,
    s1.number
FROM     Persons p
INNER JOIN  (
      /* do the calculations here */
      select PersonID
      from invoices 
      group by PersonID
      ) AS i ON i.PersonID = p.PersonID
CROSS JOIN (SELECT 5 AS number) AS s1

PersonID	number
1	5

fiddle

Your cut-down query does not actually indicate where the calculation(s) are happening. The point is that it is natural for SQL to MULTIPLY rows when joining tables. To counteract this (correct and expected) behaviour, when aggregating, you may have to do some calculations in a subquery and join that result instead of the raw table.

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server How to execute subquery once based on the group by

1条答案

相关问题

热门标签

最新问答