使用 sql 从交易详细信息中获取贷方和借方

Question

>我的表结构是这样的

From|To |Amt   |In_out
A   | B | 100  |0
B   | A | 200  |1
C   | D | 250  |0

其中最后一列定义现金流的方向 我想将输出作为

From | To | Amount Transacted | Debited | Credited
A    | B  | 300               |100      | 200
C    | D  | 250               |250      | 0

我尝试了各种查询，例如

select DISTINCT r.to,r.to,r.Amt,r.In_out
,sum(r.Amt) as credited 
from 
Records as r 
join 
Records as s 
on 
(r.from <> s.from and r.to == s.from)
where 
r.from <>s.from 
--GROUP by r.from

任何人都可以帮我解决这个问题吗？

Answer 1

在许多数据库中，您可以使用least()和greatest()：

select least(from, to), greatest(from, to),
sum(amount),
sum(case when in_out = 0 and from = least(from, to) or
in_out = 1 and to = least(from, to) 
then amount else 0
end) as debited,
sum(case when in_out = 0 and to = least(from, to) or
in_out = 1 and from = least(from, to) 
then amount else 0
end) as credited
from records
group by least(from, to), greatest(from, to);

如果您的数据库不支持这些函数，您可以使用case表达式来完成相同的操作。

Answer 2

你想要greatest() / least()：

select least(from, to) from, greatest(from, to) to,
sum(amt) AmountTransacted,
sum(case when In_out = 0 then Amt else 0 end) Debited,
sum(case when In_out = 1 then Amt else 0 end) Credited
from table t
group by least(from, to), greatest(from, to);

Answer 3

不确定你的RDBMS：Oracle或SQLite。

无论如何，这是在标准SQL中实现此目的的一种方法

诀窍是在 UNION 查询中反转 From 和 To 列，如果In_out为 1，以便正确分组帐户对

SELECT u.From, u.To, SUM(Amt) AS "Amount Transacted",
SUM(CASE WHEN In_out=0 THEN Amt ELSE 0 END) AS Debited,
SUM(CASE WHEN In_out=1 THEN Amt ELSE 0 END) AS Credited
FROM
(
SELECT In_out, From, To, Amt FROM Records WHERE In_Out = 0
UNION ALL
SELECT In_out, To, From, Amt FROM Records WHERE In_Out = 1
) u
GROUP BY u.From, u.To