暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

Oracle 分区查询-限制结果

askTom 2018-07-09
191

问题描述

我有一种情况,我试图确定发票行的可征税性。例如,如果发票行数量为6,则详细行不应超过6。

问题是,如果其中一个详细行导致累计数量超过发票数量,那么查询结果应该显示发票数量和累计数量之间的增量差异。

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行小于总体数量。如果前一个总和大于总总和,则要排除当前行。

您可以使用以下窗口子句获得前一行的累计总数:

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进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论