问题描述
我有一种情况,我试图确定发票行的可征税性。例如,如果发票行数量为6,则详细行不应超过6。
问题是,如果其中一个详细行导致累计数量超过发票数量,那么查询结果应该显示发票数量和累计数量之间的增量差异。
以下是数据的样子:
如果我取消注释 “cumulative_qty <= qty” 子句,则仅显示前4行 (因为7的累计数量超过了6的发票数量),并且当我最终使用PIVOT时,它将显示6的数量,3个是应税的,2个是免税的。这意味着查询结果将保留1的数量 (应纳税)。
Question:
如何编写此查询,以便结果看起来像这样?
问题是,如果其中一个详细行导致累计数量超过发票数量,那么查询结果应该显示发票数量和累计数量之间的增量差异。
create table tt ( ln int, pl int, id int, qty int, tqty int, tax varchar2(15), td date );复制
Insert into TT (LN, PL, ID, QTY, TQTY, TAX, td) Values (1, 9, 23149, 6, 1, 'Non-Taxable', TO_DATE('1/8/2018', 'MM/DD/YYYY')); Insert into TT (LN, PL, ID, QTY, TQTY, TAX, td) Values (1, 9, 23149, 6, 2, 'Taxable', TO_DATE('11/15/2017', 'MM/DD/YYYY')); Insert into TT (LN, PL, ID, QTY, TQTY, TAX, td) Values (1, 9, 23149, 6, 1, 'Taxable', TO_DATE('10/16/2017', 'MM/DD/YYYY')); Insert into TT (LN, PL, ID, QTY, TQTY, TAX, td) Values (1, 9, 23149, 6, 1, 'Non-Taxable', TO_DATE('9/16/2017', 'MM/DD/YYYY')); Insert into TT (LN, PL, ID, QTY, TQTY, TAX, td) Values (1, 9, 23149, 6, 2, 'Taxable', TO_DATE('5/2/2017', 'MM/DD/YYYY')); COMMIT;复制
SELECT * FROM (SELECT ln, pl, id, qty, tqty, tax, td ,SUM(tqty) over (partition by pl ORDER BY td DESC) as CUMULATIVE_QTY FROM tt ) WHERE 1=1 --cumulative_qty <= qty ORDER BY ln, td DESC;复制
以下是数据的样子:
LN PL ID QTY TQTY TAX TD CUMULATIVE_QTY 1 9 23149 6 1 Non-Taxable 1/8/2018 1 1 9 23149 6 2 Taxable 11/15/2017 3 1 9 23149 6 1 Taxable 10/16/2017 4 1 9 23149 6 1 Non-Taxable 9/16/2017 5 1 9 23149 6 2 Taxable 5/2/2017 7复制
如果我取消注释 “cumulative_qty <= qty” 子句,则仅显示前4行 (因为7的累计数量超过了6的发票数量),并且当我最终使用PIVOT时,它将显示6的数量,3个是应税的,2个是免税的。这意味着查询结果将保留1的数量 (应纳税)。
Question:
如何编写此查询,以便结果看起来像这样?
LN PL ID QTY TQTY TAX TD CUMULATIVE_QTY 1 9 23149 6 1 Non-Taxable 1/8/2018 1 1 9 23149 6 2 Taxable 11/15/2017 3 1 9 23149 6 1 Taxable 10/16/2017 4 1 9 23149 6 1 Non-Taxable 9/16/2017 5 1 9 23149 6 1 Taxable 5/2/2017 6复制
专家解答
这是一种解决方法:
返回所有行,其中的累计总数为previous行小于总体数量。如果前一个总和大于总总和,则要排除当前行。
您可以使用以下窗口子句获得前一行的累计总数:
这将为第一行返回null (它没有以前的)。所以你需要把它映射到零。
然后,您可以将其与总数进行比较。并在最终选择中返回当前运行总量和总量中的最少数量:
返回所有行,其中的累计总数为previous行小于总体数量。如果前一个总和大于总总和,则要排除当前行。
您可以使用以下窗口子句获得前一行的累计总数:
rows between unbounded preceding and 1 preceding复制
这将为第一行返回null (它没有以前的)。所以你需要把它映射到零。
然后,您可以将其与总数进行比较。并在最终选择中返回当前运行总量和总量中的最少数量:
select s.*, least (cumulative_qty, qty) cumulative from ( select ln, pl, id, qty, tqty, tax, td, sum(tqty) over (partition by pl order by td desc) as cumulative_qty, nvl(sum(tqty) over (partition by pl order by td desc rows between unbounded preceding and 1 preceding ), 0) as cumulative_qty_minus_1 from tt ) s where cumulative_qty_minus_1 <= qty order by ln, td desc; LN PL ID QTY TQTY TAX TD CUMULATIVE_QTY CUMULATIVE_QTY_MINUS_1 CUMULATIVE 1 9 23149 6 1 Non-Taxable 2018 Jan 08 1 0 1 1 9 23149 6 2 Taxable 2017 Nov 15 3 1 3 1 9 23149 6 1 Taxable 2017 Oct 16 4 3 4 1 9 23149 6 1 Non-Taxable 2017 Sep 16 5 4 5 1 9 23149 6 2 Taxable 2017 May 02 7 5 6复制
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。
评论
相关阅读
【纯干货】Oracle 19C RU 19.27 发布,如何快速升级和安装?
Lucifer三思而后行
623次阅读
2025-04-18 14:18:38
Oracle RAC 一键安装翻车?手把手教你如何排错!
Lucifer三思而后行
610次阅读
2025-04-15 17:24:06
XTTS跨版本迁移升级方案(11g to 19c RAC for Linux)
zwtian
508次阅读
2025-04-08 09:12:48
Oracle数据库一键巡检并生成HTML结果,免费脚本速来下载!
陈举超
494次阅读
2025-04-20 10:07:02
【ORACLE】记录一些ORACLE的merge into语句的BUG
DarkAthena
471次阅读
2025-04-22 00:20:37
Oracle 19c RAC更换IP实战,运维必看!
szrsu
447次阅读
2025-04-08 23:57:08
【ORACLE】你以为的真的是你以为的么?--ORA-38104: Columns referenced in the ON Clause cannot be updated
DarkAthena
442次阅读
2025-04-22 00:13:51
一页概览:Oracle GoldenGate
甲骨文云技术
438次阅读
2025-04-30 12:17:56
【活动】分享你的压箱底干货文档,三篇解锁进阶奖励!
墨天轮编辑部
436次阅读
2025-04-17 17:02:24
火焰图--分析复杂SQL执行计划的利器
听见风的声音
388次阅读
2025-04-17 09:30:30