从具有重复项的连接中的另一个表返回Sum

Question

我有以下两张表:

tblEventCustomers

EventCustomerId EventId CustomerId  InvoiceLineId
1002            100     5           21
1003            100     6           21
1004            100     7           22
1005            101     9           23

tblInvoiceLines

InvoiceLineId   Quantity    Price
21              2           25
22              1           12.5
23              1           34

我想返回一个事件的客户数量和该事件的发票行总数:

EventId     No. Delegates   Total
100         3               37.5
101         1               34

我已经尝试了以下功能:

CREATE FUNCTION dbo.udfInvoiceLineTotal
(
    @eventId AS INT
)
RETURNS MONEY
BEGIN
    DECLARE @returnAmount AS MONEY;
    SET @returnAmount = (
    select sum(Price) from tblInvoiceLines as IL
    where il.InvoiceLineId in
    (
        SELECT InvoiceLineId
        FROM tblEventCustomers
        where EventId = @eventId
    )   
    )
    RETURN @returnAmount;
END

并按如下方式使用:

select      ed.EventId, 
            COUNT(1),
            dbo.udfInvoiceLineTotal(ed.EventId) from tblEventCustomers as ED
inner join  tblInvoiceLines as IL
on          IL.InvoiceLineId = ED.InvoiceLineId
group by    ed.EventId

这返回了我想要的结果，但是我想看看我是否缺少任何方法以更ANSI SQL的方式做到这一点，即使用子查询而不是数据库功能?

下面的方法在sum中返回我的重复项:

select  ed.EventId,
        SUM(il.Price),
        COUNT(1)
from    tblEventCustomers as ed
inner join tblInvoiceLines as il
on      ed.InvoiceLineId = il.InvoiceLineId
group by (ed.EventId)

编辑

为所有回答的人道歉，我意识到我在原始数据集中有一个错字，这意味着一些建议的方法适用于我最初呈现的数据集，但不适用于完整的数据集。

Answer 1

如果您像这样修改您的最后一种方法，它应该会给您期望的结果:

select  ed.EventId,
        SUM(il.Price/il.quantity)
from    tblEventCustomers as ed
inner join tblInvoiceLines as il
on      ed.InvoiceLineId = il.InvoiceLineId
group by (ed.EventId)
having COUNT(ed.EventCustomerId) > 5

加上SUM(il.quantity)

Answer 2

如果我理解正确，你需要一个子查询来摆脱你所说的重复，这是由EventCustomerId列引起的，你不包括。

select
  ec.eventid,
  count (ec.CustomerId),
  sum(il.price)
from
  (select
     eventid,
     customerid,
     invoicelineid
   from
     tblEventCustomers
    group by
      eventid,
      customerid,
      invoicelineid)
   EC
inner join tblInvoiceLines IL
  on ec.invoicelineid = il.invoicelineid
group by
  ec.eventid

SQL小提琴

Answer 3

这对你有用吗?它得到你要找的结果

SELECT c.EventId, SUM(Quantity), SUM(i.price)
FROM tblInvoiceLines i
   JOIN (SELECT DISTINCT 
         EventId, CustomerId, InvoiceLineId
        FROM tblEventCustomers) c ON i.InvoiceLineId = c.InvoiceLineId
GROUP BY c.EventId

Answer 4

您可能希望查看cte(公共表表达式)，其中您预先计算/分组一个表，然后根据列将结果与另一个表连接，并一起显示结果。它类似于子查询，但我更喜欢CTE，因为它们更容易阅读。

看这些链接作为参考，希望这有助于:

https://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/

http://blogs.msdn.com/b/craigfr/archive/2007/10/18/ctes-common-table-expressions.aspx

Answer 5

需要在连接之前对结果进行预聚合。您可以为此使用子查询:

select c.EventId, c.NumCustomers, ci.price
from (select c.EventId, count(*) as NumCustomers
      from tblEventCustomers c
      group by c.EventId
     ) c left join
     (select c.EventId, sum(i.price) as price
      from (select distinct EventId, InvoiceLineId
            from tblEventCustomers c
           ) c join
           tblInvoiceLines i
           on i.InvoiceLineId = c.InvoiceLineId
      group by c.EventId
     ) ci
     on c.EventId = ci.EventId;

令人困惑的是，您必须将客户表连接到发票表才能获得事件id。