tqsl - 从选择查询结果中作为变量在子查询中操作？

Question

对于每个用户，我想产生一个聚合结果。然而，我一直遇到子查询返回多个结果的问题。

SELECT **[user]**, 
   ((SELECT SUM(amount) 
          FROM transactions 
          WHERE [user] = **[user]** 
          AND [type] = 'credit' ) - 
            (SELECT SUM(amount) 
            FROM transactions 
            WHERE [user] = **[user]** 
            AND [type] = 'debit' )) 
     FROM transactions

我该如何从开始的 select 中获取 user，然后将其作为变量 user 在子查询中进行操作？

- Paul

---

感谢@sll清理了代码的演示:) - Paul

Answer 1

使用CASE来简化求和：

SELECT user,SUM(CASE type WHEN 'credit' THEN amount WHEN 'debit' THEN -amount END)
from transactions
GROUP BY user

如果只有信用卡和借记卡是有效的类型，则可以进一步简化为：

CASE type WHEN 'credit' THEN amount ELSE -amount END

---

使用子查询进行求和操作时，仍然从交易表中选择用户的答案将会对每一行在交易表中进行多次求和操作 - 因此，如果一个用户有5个交易，它们将会计算5次总和(并显示5个结果行)，我想你并不希望出现这种情况。

- Damien_The_Unbeliever

---

感谢@Damien_The_Unbeliever！之前我的PHP和TSQL解决方案需要超过30秒才能完成...但现在它已经大幅缩短到不到1秒!!!非常感谢你！ - Paul

Answer 2

select username,
       SUM(amount * case when operation = 'credit' then 1 else -1 end) as balance
from #transaction
group by username

- Rubens Farias

Answer 3

我喜欢@Rubens的回答，但是...

你的数据结构需要改进。

实际上，为每个交易存储单词“credit”或“debit”，更不用说必须使用case语句来将其转换为可用值（1或-1），这是代价高昂的。

如果您的交易表跟踪数量，则可以使用该数量而不是存储和评估“credit”和“debit”。正数数量（例如，我买了2个耙子等）和负数数量（例如，-1个耙子，这意味着您退货或获得了一个耙子的信用）可以在不执行任何case语句评估的情况下进行求和。

- Chains

Answer 4

试试这个：

 SELECT user, 
    (SELECT SUM(amount) 
     FROM transactions 
     WHERE [user] = t.User
        AND [type] = 'credit') 
     - 
    (SELECT SUM(amount) 
     FROM transactions 
     WHERE [user] = t.User
       AND [type] = 'debit' )) 
 FROM transactions t

或者这样：

 SELECT user, 
    Sum (Case type When 'credit' then Amount End) credit,
    Sum (Case type When 'debit'  then Amount End) debit,
    Sum (Case type When 'credit' then Amount
                   When 'debit'  then -Amount End) NetAmount    
 FROM transactions t
 Group By User

- Charles Bretana

---

谢谢Charles的想法，但我选择了稍微更简洁的选项 :) - Paul

Answer 5

可能

SELECT t.user, 
((SELECT SUM(amount) FROM transactions WHERE [user] = t.user AND [type] = 'credit' ) -
(SELECT SUM(amount) FROM transactions WHERE [user] = t.user AND [type] = 'debit' )) 
FROM transactions t

- marc

---

很遗憾，@Marc，这个选项没有起作用。它没有为每个用户提供单一的聚合结果。无论如何，还是谢谢您 :) - Paul

Answer 6

在@Rubens Farias的解决方案上进行了修改：

SELECT
  t.[user],
  SUM(t.amount * x.factor)
FROM transactions t
  INNER JOIN (
    SELECT 'credit', 1 UNION ALL
    SELECT 'debit', -1
  ) x (type, factor) ON t.type = x.type
GROUP BY t.[user]

- Andriy M

---

你的子查询中需要为列命名。 - t-clausen.dk

@t-clausen.dk：列的别名是在子查询的别名后面的括号中分配的：x（type，factor）。 - Andriy M