时间戳

首页 > 解决方案 > 从具有多行相同id的表中获取每个id一行和多列的表，并根据条件计算总和

sql - 从具有多行相同id的表中获取每个id一行和多列的表，并根据条件计算总和

问题描述

这是我的佣金表，其中一些 id 有两个条目，而另一些只有一个。如果在原始佣金日期后 30 天内有反向输入，则应撤销原始佣金，但如果反向输入是在 30 天之后，则应忽略。

线索ID 佣金日期 委员会
22940 2020 年 1 月 3 日 30
22940 2020 年 3 月 30 日 -30
22941 2020 年 4 月 30 日 30
22942 2020 年 3 月 31 日 60
22942 15/06/2020 -60

因此，这是我想要去的最后一张桌子。

线索ID 总佣金
22940 0
22941 30
22942 60

我为查询的第一部分编写的代码应该为任何lead_id 生成一行，存储佣金日期和佣金金额（如果存在）（某些id 只有一个条目，没有反向佣金）。这是代码

WITH TAB_RN AS
(
      SELECT commissions.lead_id, commissions.commission_date, commissions.commission_amount, ROW_NUMBER() OVER (PARTITION BY commissions.lead_id, commissions.commission_date) AS RN
  FROM commissions
)
SELECT T1.lead_id, 
       T1.commission_date AS DATE1, T1.commission_amount AS amount1
       T2.commission_date AS DATE2, T2.commission_amount AS amount2
FROM TAB_RN T1
LEFT JOIN TAB_RN T2 ON T1.lead_id = T2.lead_id AND T1.commission_date = T2.commission_date AND T2.RN = 2
WHERE T1.RN = 1

它应该产生这个结果

线索ID 日期 1 金额1 日期 2 金额2
22940 2020 年 1 月 3 日 30 2020 年 3 月 30 日 -30
22941 2020 年 4 月 30 日 30
22942 2020 年 3 月 31 日 60 15/06/2020 -60

但相反，我回来了

线索ID 日期 1 金额1 日期 2 金额2
22940 2020 年 1 月 3 日 30
22940 2020 年 3 月 30 日 -30
22941 2020 年 4 月 30 日 30
22942 2020 年 3 月 31 日 60
22942 15/06/2020 -60

然后我想计算日期 1 和 2 之间的差异，如果差异小于 30 天总金额 1 和金额 2，否则只应计算金额 1。

最后，我想在commissions.company = Companies.id 上加入公司表并按公司x 过滤

标签： sqlpostgresql

解决方案

把它分解

选择佣金

select lead_id, commission_date, commission
from commissions
where commission > 0

然后将其加入到有效的返还佣金中

select commissions.lead_id, commissions.commission_date, commissions.commission + ret.commission as commission_total
from commissions
join (
  -- returned commissions have negative value.
  select lead_id, commission_date, commission
  from commissions
   where commsion < 0
) ret on commissions.lead_id = ret.ldead_id
       and   commissions.commission= ret.commission * -1
       and datediff(day,ret.commission_date,comm commissions.commission_date) <=30
where commission > 0

推荐阅读