TSQL 使用 Group By 连接到另一个表

Question

我目前拥有的伪代码是

Cost + CostTax = CostActual

但是，我需要它是：

Cost + Tax - TotalSumDiscounts = CostActual

我在连接 3 个表时有一个 SQL Select 语句，用于计算 Cost + Tax = CostActual。

SELECT     dbo.XMP_EventUsers.ID, dbo.XMP_EventUsers.EventID, dbo.XMP_EventUsers.UserID,
CONVERT(VARCHAR(20),dbo.XMP_EventUsers.DateCreated, 100) 
AS DateCreated, CONVERT(VARCHAR, dbo.XMP_Event.Cost, 1) AS Cost, dbo.Users.FirstName, dbo.Users.LastName, 
dbo.XMP_Event.Cost + dbo.XMP_Event.CostTax - dbo.XMP_EventUsers.Paid AS Outstanding, 
dbo.XMP_Event.Cost +     dbo.XMP_Event.CostTax AS CostActual
FROM         dbo.XMP_Event INNER JOIN
dbo.XMP_EventUsers ON dbo.XMP_Event.ID = dbo.XMP_EventUsers.EventID INNER JOIN
dbo.Users ON dbo.XMP_EventUsers.UserID = dbo.Users.UserID

另一个表记录了应用于总成本的折扣。此查询汇总要应用的折扣。

    SELECT UserID, SUM([Amount]) AS Amount
FROM [dbo].[XMP_EventPromoUsers]
GROUP BY UserID

如何将第二个结果连接到第一个结果，以便折扣总数从每个记录的 select 语句中的最终成本中减去？

感谢您的帮助！

-R

以下答案 1

查询 1 返回的示例结果：

ID|EventID|UserID|DateCreated|Cost|FirstName|LastName|Outstanding|CostActual|FinalValue|
51|14-----|41----|Aug 11 2011|0.00|John-----|Smith---|-120-------|0.00------|-10-------|

查询 2

UserID-|Amount----|

41-----|10--------|

96-----|30--------|

Answer 1

SELECT     
    dbo.XMP_EventUsers.ID, dbo.XMP_EventUsers.EventID, dbo.XMP_EventUsers.UserID,
    CONVERT(VARCHAR(20),dbo.XMP_EventUsers.DateCreated, 100) AS DateCreated, 
    CONVERT(VARCHAR, dbo.XMP_Event.Cost, 1) AS Cost, 
    dbo.Users.FirstName, dbo.Users.LastName, 
    dbo.XMP_Event.Cost + dbo.XMP_Event.CostTax - dbo.XMP_EventUsers.Paid AS Outstanding, 
    dbo.XMP_Event.Cost +     dbo.XMP_Event.CostTax AS CostActual,
    dbo.XMP_Event.cost + dbo.XMP_Event.CostTax - Users2.Amount AS FinalValue
FROM    
    dbo.XMP_Event 
    INNER JOIN dbo.XMP_EventUsers 
        ON dbo.XMP_Event.ID = dbo.XMP_EventUsers.EventID 
    INNER JOIN dbo.Users 
        ON dbo.XMP_EventUsers.UserID = dbo.Users.UserID
    INNER JOIN (SELECT UserID, SUM([Amount]) AS Amount
        FROM [dbo].[XMP_EventPromoUsers]
        GROUP  BY UserID) Users2
        ON dbo.Users.UserID = Users2.UserID