需要一些帮助从查询中获取表，以便将来在我自己的数据库中使用

Question

Users

User ID Site ID Name
1   1   Arthur
2   1   Aaron
2   2   Brett

交易

Transaction ID  User ID Site ID Transaction Type     Trans Date    Amount       
4   1   1   Sale    1/1/2017    120      1/1/2017  120
6   1   1   Refund  1/7/2017    -120     1/7/2017  -120
7   2   2   Sale    1/5/2017    90       1/5/2017  90 
9   2   1   Sale    12/1/2016   30       12/1/2016  30
10  2   1   Sale    1/1/2017    30       1/1/2017   30
11  2   1   Sale    2/1/2017    30       2/1/2017   30
12  2   1   Refund  2/7/2017    -30      2/7/2017   -30

需要编写一个查询，该查询将返回包含以下列的表

User ID, Site ID, User Name, Total Sales, Total Refunds, Net Amount Collected

也许我需要一个内部连接函数或其他连接？我需要连接的另一部分才能恢复具有正确列的表，尝试过这个，但我认为只有一半。

with cte_sales as
(
    select
    t.[User Id],
    t.[Site Id],
    sum(t.Amount) as [Total Sales]
    from Transactions t
    where t.[Transaction Type] = 'Sale'
    group by t.[User Id],
             t.[Site Id]
),
cte_refunds as
(
    select
    t.[User Id],
    t.[Site Id],
    sum(t.Amount) as [Total Refunds]
    from Transactions t
    where t.[Transaction Type] = 'Refund'
    group by t.[User Id],
             t.[Site Id]
)
ith cte_sales as
(
    select
    t.[User Id],
    t.[Site Id],
    sum(t.Amount) as [Total Sales]
    from Transactions t
    where t.[Transaction Type] = 'Sale'
    group by t.[User Id],
             t.[Site Id]
),
cte_refunds as
(
    select
    t.[User Id],
    t.[Site Id],
    sum(t.Amount) as [Total Refunds]
    from Transactions t
    where t.[Transaction Type] = 'Refund'
    group by t.[User Id],
             t.[Site Id]
)

如果我能获得一个基于一个查询的表，其中包含所描述的列，那就太棒了，但目前正在刷新我的连接。

Answer 1

您可以联接这两个表并使用条件聚合：

SELECT
    u.[User ID],
    u.[Site ID],
    u.Name as [User Name],
    SUM(CASE WHEN t.[Transaction Type] = 'Sale'   THEN t.Amount ELSE 0 END) AS [Total Sales],
    SUM(CASE WHEN t.[Transaction Type] = 'Refund' THEN t.Amount ELSE 0 END) AS [Total Refunds],
    SUM(t.Amount) AS [Net Amount Collected]
FROM Users u
INNER JOIN Transactions t
    ON t.[User ID] = u.[User ID] AND t.[Site ID] = u.[Site ID]
GROUP BY
    u.[User ID],
    u.[Site ID],
    u.Name