代码之家 › 专栏 › 技术社区 › Finbarr

Postgres/SQL:针对支付源建模收费和退款

join postgresql sql

Finbarr · 技术社区 · 11 年前

我有以下模型,我想写一个查询,返回未使用的信用。信用卡有很多费用,而这些费用又有很多退款。到目前为止,我的查询尝试如下,但我发现了一个问题,需要一些帮助。所有的 amount 列为正数。

Credit
id (integer)
amount (decimal)

Charge
id (integer)
credit_id (integer)
amount (decimal)

Refund
id (integer)
charge_id (integer)
amount (decimal)

我的查询如下所示:

SELECT credits.*,
"credits"."amount" - coalesce(sum("charges"."amount"), 0) + coalesce(sum("refunds"."amount"), 0) AS unspent_amount 
FROM "credits"
LEFT OUTER JOIN "charges" ON "charges"."credit_id" = "credits"."id"
LEFT OUTER JOIN "refunds" ON "refunds"."charge_id" = "charges"."id"
GROUP BY "credits"."id"
HAVING "credits"."amount" > coalesce(sum("charges"."amount"), 0) - coalesce(sum("refunds"."amount"), 0) LIMIT 1

我遇到的问题是,如果一项收费有很多退款,那么收费金额的总和将是N*,其中N是退款次数。我只想为每个收费id计算一次收费金额。

最终,我想要credits.amount>费用总额_against_credits.amount-refrends_for_these_charges.mamount。我如何实现这一点?

编辑:

以下是一些将重现问题的样本记录:

credits
id 1
amount 25.0

charges
id 1
credit_id 1
amount 25.0

refunds
id 1
charge_id 1
amount 20.0
-----------
id 2
charge_id 1
amount 5.0

编辑2:

预期输出:

credits:
1 row:
id: 1, amount: 25.0, unspent_amount: 25.0

1 回复 | 直到 11 年前

John Woo 11 年前

SELECT  a.ID,
        (a.amount - COALESCE(b.totalCharges, 0)) + COALESCE(c.totalRefunds, 0) AS unspent_amount
FROM    credits AS a
        LEFT JOIN
        (
            SELECT  credit_ID, SUM(amount) totalCharges
            FROM    charges
            GROUP   BY credit_ID
        ) AS b  ON a.ID = b.credit_ID
        LEFT JOIN
        (
            SELECT  aa.credit_ID, SUM(bb.amount) totalRefunds
            FROM    charges AS aa
                    LEFT JOIN Refund AS bb
                        ON  aa.ID = bb.Charge_ID
            GROUP   BY aa.credit_ID
        ) AS c  ON b.credit_ID = c.credit_ID
--  WHERE   clause ...here....

推荐文章

Ishaan Adarsh · 未知的META规范,无法验证。[规范v1.0.1]

1 年前

tggtsed · PostgreSQL从平均值中排除值

1 年前

Dawid · 为什么我不能在子查询中使用表别名?

1 年前

LAZ · ORDER BY id DESC LIMIT 100返回第一行而不是最后一行

2 年前

Mohammed Shaheer · 查找列中的数据是否满足sql中的数据类型条件

2 年前

AKang123. · Postgres:重复行,但更新json中的字段

2 年前

CraZyCoDer · 在PostgreSQL中锁定潜在事务

2 年前

ranebec · 计数时如何返回0而不是null?

2 年前

Ramón J Romero y Vigil · 将没有时区列的postgres时间戳转换为日期

2 年前

Flo · 分组依据中的SQL大小写

2 年前