如何将每行SUM（）转换为另一列

I have this table

我有这张桌子

 | ID_prim    | ID (FKey)   | Date         | Moved Items  |
 |:-----------|:------------|-------------:|:------------:|
 | 1003       | 12_1        |    nov 2013  |    2         |
 | 1003       | 12_2        |    okt 2013  |    3         |
 | 1003       | 12_3        |    dec 2014  |    5         |
 | 1003       | 12_4        |    feb 2015  |    10        |
 | 1003       | 12_5        |    apr 2012  |    1         |
 | 1003       | 12_11       |    jan 2011  |    5         |

I want to query the same table as follows:

我想查询同一个表如下：

Order the Date by desc
通过desc订购日期
Sum each 'Moved Item" per row
每行对每个'Moved Item'求和
Stop the query if the Sum reaches my desired amount
如果Sum达到我想要的金额，则停止查询
My desired amount starts from the MAX 'Summed Total' (26) and subtracts the amount I want (16)
我想要的金额从MAX'Summed Total'开始（26）并减去我想要的金额（16）

Like so

像这样

| ID_prim    | ID (FKey)   | Date         | Moved Items  | Summed Total |
|:-----------|:------------|-------------:|:------------:|:------------:|
| 1003       | 12_4        |    feb 2015  |     10       |     26
| 1003       | 12_3        |    dec 2014  |     5        |     16
| 1003       | 12_3        |    nov 2013  |     2        |     11 <
| 1003       | 12_4        |    okt 2013  |     3        |     9 
| 1003       | 12_5        |    apr 2012  |     1        |     6
| 1003       | 12_11       |    jan 2011  |     5        |     5

I want to stop the query when i reach "Summed Total" (26) - 16 = 10. So Show me everything from 10 > I would only get these values in the database.

当我达到“Summed Total”（26） - 16 = 10时，我想停止查询。所以从10>向我显示所有内容我只会在数据库中获取这些值。

| ID_prim    | ID (FKey)   | Date         | Moved Items  | Summed Total |
|:-----------|:------------|-------------:|:------------:|:------------:|
| 1003       | 12_4        |    feb 2015  |     10       |     26
| 1003       | 12_3        |    dec 2014  |     5        |     16
| 1003       | 12_3        |    nov 2013  |     2        |     11

What I have is the following

我所拥有的是以下内容

  SELECT
  T1.ID_prim, T1.ID as ID (FKey), T1.Moved_Items as Moved Items, t1.Date, SUM(T2.MOVEMENTQTY) AS Summed Total
  FROM Table1 T1
  INNER JOIN Table1 T2 ON T2.ID <= T1.ID
    inner join table2 inout on T1.ID_prim = inout.ID_prim
    AND T2.ID_prim = inout.ID_prim
    AND T2.ID_prim = T1.ID_prim
  where t1.ID_prim = 1003
  and t2.ID_prim = 1003
  and inout.ISSOTRX = 'N'
  GROUP BY T1.ID_prim, T1.Moved Items, t1.Date
  HAVING SUM(T2.Moved Items) <= 16
  order by  t1.UPDATED desc

But the sum doesn't really work. Can anyone help me out to make the SQL statement for Oracle DB that will print my Desired table?

但总和并没有真正起作用。任何人都可以帮我制作将打印我的Desired表的Oracle DB的SQL语句吗？

1 个解决方案

#1

Based on OP's clarifications via comments on the question, it could be done using SUM() analytic function to get the running total, and then filter it based on the condition.

根据OP对该问题的评论进行澄清，可以使用SUM（）分析函数来获取运行总计，然后根据条件对其进行过滤。

Table:

表：

SQL> SELECT * FROM t;

   ID_PRIM ID    DT             MOVED
---------- ----- --------- ----------
      1003 12_1  01-NOV-13          2
      1003 12_2  01-OCT-13          3
      1003 12_3  01-DEC-14          5
      1003 12_4  01-FEB-15         10
      1003 12_5  01-APR-12          1
      1003 12_11 01-JAN-11          5

6 rows selected.

SQL>

Running total

跑步总数

SQL> SELECT t.*, SUM(moved) OVER(ORDER BY dt) sm FROM t ORDER BY dt DESC;

   ID_PRIM ID    DT             MOVED         SM
---------- ----- --------- ---------- ----------
      1003 12_4  01-FEB-15         10         26
      1003 12_3  01-DEC-14          5         16
      1003 12_1  01-NOV-13          2         11
      1003 12_2  01-OCT-13          3          9
      1003 12_5  01-APR-12          1          6
      1003 12_11 01-JAN-11          5          5

6 rows selected.

SQL>

Desired output

期望的输出

SQL> WITH DATA AS
  2    ( SELECT t.*, SUM(moved) OVER(ORDER BY dt) sm FROM t ORDER BY dt DESC
  3    )
  4  SELECT * FROM data WHERE sm >= 16;

   ID_PRIM ID    DT             MOVED         SM
---------- ----- --------- ---------- ----------
      1003 12_4  01-FEB-15         10         26
      1003 12_3  01-DEC-14          5         16

SQL>

Please note that, nov 2013 is not a date, it is a string. Since you want to sort on the basis of date, you must always use TO_DATE to explicitly convert it into date. Anyway, I used TO_DATE to create the sample data.

请注意，2013年11月不是日期，它是一个字符串。由于您希望根据日期进行排序，因此必须始终使用TO_DATE将其显式转换为日期。无论如何，我使用TO_DATE来创建示例数据。

Update OP wants to subtract his desired value from the MAX value of the summed up values at run time.

更新OP想要在运行时从总计值的MAX值中减去他想要的值。

SQL> WITH DATA AS
  2    ( SELECT t.*, SUM(moved) OVER(ORDER BY dt) sm FROM t ORDER BY dt DESC
  3    )
  4  SELECT * FROM DATA t WHERE sm >
  5    (SELECT MAX(sm) FROM data
  6    ) - 16 ;

   ID_PRIM ID    DT             MOVED         SM
---------- ----- --------- ---------- ----------
      1003 12_4  01-FEB-15         10         26
      1003 12_3  01-DEC-14          5         16
      1003 12_1  01-NOV-13          2         11

SQL>

In the updated query, MAX(sm) returns 26, and then the rows are filtered on the condition WHERE sm > MAX(sm) -16 which means return all the rows where the 'sm' value is greater than 26 -16 i.e. 10. You could use a substitution variable to input the value 16 at run time.

在更新的查询中，MAX（sm）返回26，然后在WHERE sm> MAX（sm）-16条件下过滤行，这意味着返回'sm'值大于26 -16的所有行，即10您可以使用替换变量在运行时输入值16。

#1