sql - postgres子查询中列的动态生成
问题描述
下面的查询给出了特定年份的名称、ID、订阅、打开、关闭、总金额的详细信息,即,posted_date 是查询的输入。
select la.name,la.id,la.parent_id,la.is_group,tb1.opening op_1,tb1.closing cl_1,
coalesce((select sum(j.amount) from journal j,voucher v , ledger_account le where j.voucher_id=v.id and le.id=j.ledger_account_id and la.id=le.id and
v.posted_date::date>='2020-04-01' and v.posted_date::date<='2021-03-31'),0) balance_2020
from ledger_account la left join trialb tb1 on tb1.ledger_account_id=la.id and tb1.fy_id=1
上面的查询给出了 2020 年的总数据和余额,例如:-如果我需要 2005 年的余额,我再次需要多次粘贴以下逻辑
coalesce((select sum(j.amount) from journal j,voucher v , ledger_account le where j.voucher_id=v.id and le.id=j.ledger_account_id and la.id=le.id and v.posted_date::date>='2020-04-01' and v.posted_date::date<='2021-03-31'),0) balance_2020
并将 v.posted 日期和列名更改为
v.posted_date::date>='2005-04-01' 和 v.posted_date::date<='2006-03-31'),0) balance_2005等等直到 2020 年,几乎 15 次获得总余额,其中查询的大小每年都在增加,并且所花费的时间。
那么是否有任何替代或可能的方式使用这些列 balance_2005,balance_2006.. 可以根据给定查询的输入在必要时动态生成?
解决方案
将子查询移动到 from 子句并使用条件聚合来获取各种总和:
select
la.name,
la.id,
la.parent_id,
la.is_group,
tb1.opening op_1,
tb1.closing cl_1,
sums.balance_2018,
sums.balance_2019,
sums.balance_2020
from ledger_account la
left join trialb tb1 on tb1.ledger_account_id = la.id and tb1.fy_id = 1
left join
(
select
j.ledger_account_id,
sum(case when v.posted_date::date >= date '2018-04-01' and v.posted_date::date <= date '2019-03-31') then j.amount else 0 end) as balance_2018,
sum(case when v.posted_date::date >= date '2019-04-01' and v.posted_date::date <= date '2020-03-31') then j.amount else 0 end) as balance_2019,
sum(case when v.posted_date::date >= date '2020-04-01' and v.posted_date::date <= date '2021-03-31') then j.amount else 0 end) as balance_2020
from journal j
join voucher v on v.id = j.voucher_id
group by j.ledger_account_id
) sums on sums.ledger_account_id = la.id
order by la.name;
如果您不想要固定年份,则不能使用列,而必须使用行。您需要计算从日期到财政年度,但这只是从中减去三个月。
select
la.name,
la.id,
la.parent_id,
la.is_group,
tb1.opening op_1,
tb1.closing cl_1,
sums.fiscal_year
sums.balance
from ledger_account la
left join trialb tb1 on tb1.ledger_account_id = la.id and tb1.fy_id = 1
left join
(
select
j.ledger_account_id,
extract(year from v.posted_date - interval '3 months') as fiscal_year
sum(j.amount) as balance
from journal j
join voucher v on v.id = j.voucher_id
group by j.ledger_account_id, extract(year from v.posted_date - interval '3 months')
) sums on sums.ledger_account_id = la.id
order by la.name, sums.fiscal_year;
让您的应用循环处理帐户的年度数据。
如果您想避免获得非常大的结果集并将其限制在某些年份,您可以添加此标准,例如
...
where extract(year from v.posted_date - interval '3 months') between 2010 and 2020
group by j.ledger_account_id, extract(year from v.posted_date - interval '3 months')
...
推荐阅读
- yarnpkg - 如何使用 Yarn 工作区将共享依赖项添加到 monorepo?
- javascript - 共享 ECDH 密钥,浏览器 + NodeJS
- html - 阻止可拖动元素出现在下面,并在图像中制作内容
- flutter - 使用 Dart 对同一引用进行连续方法调用的最佳实践
- arrays - 如何在 Svelte 中创建一个更新到本地存储的嵌套数组存储?
- java - lib/Pages/ChattingPage.dart:15:8: 错误: 未找到: 'dart:html' import 'dart:html' as html;
- react-native - 在 RN 中集成 HMS 广告
- websocket - 为什么在 socket.emit 上使用 socket.broadcast.emit 函数
- javascript - Owl-carousel 每个项目显示很多 3 个图像 - Laravel
- vb.net - 使用 DataGridViewCellStyle 在 DatagridView 中设置特殊属性