<bdo id='uTnhY'></bdo><ul id='uTnhY'></ul>

      1. <small id='uTnhY'></small><noframes id='uTnhY'>

      2. <tfoot id='uTnhY'></tfoot>

      3. <i id='uTnhY'><tr id='uTnhY'><dt id='uTnhY'><q id='uTnhY'><span id='uTnhY'><b id='uTnhY'><form id='uTnhY'><ins id='uTnhY'></ins><ul id='uTnhY'></ul><sub id='uTnhY'></sub></form><legend id='uTnhY'></legend><bdo id='uTnhY'><pre id='uTnhY'><center id='uTnhY'></center></pre></bdo></b><th id='uTnhY'></th></span></q></dt></tr></i><div id='uTnhY'><tfoot id='uTnhY'></tfoot><dl id='uTnhY'><fieldset id='uTnhY'></fieldset></dl></div>
        <legend id='uTnhY'><style id='uTnhY'><dir id='uTnhY'><q id='uTnhY'></q></dir></style></legend>

        Oracle 上的条件求和

        时间:2023-09-18

        <i id='sk8sX'><tr id='sk8sX'><dt id='sk8sX'><q id='sk8sX'><span id='sk8sX'><b id='sk8sX'><form id='sk8sX'><ins id='sk8sX'></ins><ul id='sk8sX'></ul><sub id='sk8sX'></sub></form><legend id='sk8sX'></legend><bdo id='sk8sX'><pre id='sk8sX'><center id='sk8sX'></center></pre></bdo></b><th id='sk8sX'></th></span></q></dt></tr></i><div id='sk8sX'><tfoot id='sk8sX'></tfoot><dl id='sk8sX'><fieldset id='sk8sX'></fieldset></dl></div>
      4. <legend id='sk8sX'><style id='sk8sX'><dir id='sk8sX'><q id='sk8sX'></q></dir></style></legend>
        1. <small id='sk8sX'></small><noframes id='sk8sX'>

          <tfoot id='sk8sX'></tfoot>

                <tbody id='sk8sX'></tbody>
                  <bdo id='sk8sX'></bdo><ul id='sk8sX'></ul>
                  本文介绍了Oracle 上的条件求和的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  我正在尝试使用条件 SUM 进行查询.SUM 需要超过 15,然后重置它.像这样:

                  Im tring to make a query with a conditional SUM. The SUM needs to get more than 15, after that reset it. Like this:

                  A | 3 | 3 
                  B | 7 | 10 
                  C | 6 | 16  -- ====
                  D | 5 | 5 
                  E | 9 | 14
                  F | 3 | 17  -- ====
                  G | 8 | 8
                  

                  我该怎么做?

                  推荐答案

                  作为递归 SQL 的替代方案,您还可以使用 SQL MODEL 子句.就我个人而言,我觉得这比递归 SQL 更容易阅读,尽管它更难编写(因为大多数人,像我一样,需要查找语法).

                  As an alternative to recursive SQL, you can also use the SQL MODEL clause. Personally, I find this a little easier to read than recursive SQL, though it is harder to write (because most people, like me, need to look up the syntax).

                  -- "test_data" is just a substitute for your real table, which I don't have
                  -- it is just so people without your table can run this example and would
                  -- not be part of your real solution.
                  with test_data ( sort_col, addend ) as
                  ( SELECT 'A', 3 FROM DUAL UNION ALL
                   SELECT 'B', 7 FROM DUAL UNION ALL
                   SELECT 'C', 6 FROM DUAL UNION ALL
                   SELECT 'D', 5 FROM DUAL UNION ALL
                   SELECT 'E', 9 FROM DUAL UNION ALL
                   SELECT 'F', 3 FROM DUAL UNION ALL
                   SELECT 'G', 8 FROM DUAL ),
                  -- Solution begins here
                  sorted_inputs ( sort_col, sort_order, addend, running_sum_max_15) as
                  ( SELECT sort_col, row_number() over ( order by sort_col ) sort_order, addend, 0 from test_data )
                  SELECT sort_col, addend, running_sum_max_15
                  from sorted_inputs
                  model 
                  dimension by (sort_order)
                  measures ( sort_col, addend, running_sum_max_15 )
                  rules update
                  ( running_sum_max_15[1] = addend[1],
                    running_sum_max_15[sort_order>1] = 
                            case when running_sum_max_15[CV(sort_order)-1] < 15 THEN 
                               running_sum_max_15[CV(sort_order)-1] ELSE 0 END+addend[CV(sort_order)]
                  )
                  

                  结果

                  +----------+--------+--------------------+
                  | SORT_COL | ADDEND | RUNNING_SUM_MAX_15 |
                  +----------+--------+--------------------+
                  | A        |      3 |                  3 |
                  | B        |      7 |                 10 |
                  | C        |      6 |                 16 |
                  | D        |      5 |                  5 |
                  | E        |      9 |                 14 |
                  | F        |      3 |                 17 |
                  | G        |      8 |                  8 |
                  +----------+--------+--------------------+
                  

                  这篇关于Oracle 上的条件求和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  上一篇:oracle中的自动增量到已经创建的表 下一篇:尝试为错误 ORA-01804 检索文本时出错

                  相关文章

                    <i id='C10Hm'><tr id='C10Hm'><dt id='C10Hm'><q id='C10Hm'><span id='C10Hm'><b id='C10Hm'><form id='C10Hm'><ins id='C10Hm'></ins><ul id='C10Hm'></ul><sub id='C10Hm'></sub></form><legend id='C10Hm'></legend><bdo id='C10Hm'><pre id='C10Hm'><center id='C10Hm'></center></pre></bdo></b><th id='C10Hm'></th></span></q></dt></tr></i><div id='C10Hm'><tfoot id='C10Hm'></tfoot><dl id='C10Hm'><fieldset id='C10Hm'></fieldset></dl></div>
                    <tfoot id='C10Hm'></tfoot>
                    <legend id='C10Hm'><style id='C10Hm'><dir id='C10Hm'><q id='C10Hm'></q></dir></style></legend>

                    <small id='C10Hm'></small><noframes id='C10Hm'>

                    • <bdo id='C10Hm'></bdo><ul id='C10Hm'></ul>