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

查理·达尔

用户

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]
)

如果我能得到一个基于单个查询的表，其中描述的列会很棒，但目前正在刷新我的连接。

专卖店

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

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。