我有这张桌子
| 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 |
我想查询同一个表,如下所示：
>通过desc命令日期
>每行总计每个“移动物品”
>如果Sum达到我想要的金额,停止查询
>我的期望金额从MAX’Summed Total'(26)开始,并减去我想要的金额(16)
像这样
| 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
当我达到“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
我所拥有的是如下
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
但是这笔钱真的没有办法.
任何人都可以帮助我做出Oracle DB的sql语句,这将打印我所需的表？
解决方法
基于OP的澄清,通过对问题的评论,可以使用SUM()分析函数来获取运行总数,然后根据条件进行过滤.
表：
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>
运行总计
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>
所需输出
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>
请注意,nov 2013不是一个日期,它是一个字符串.由于您希望按日期排序,因此您必须始终使用TO_DATE将其明确转换为日期.无论如何,我用TO_DATE创建样本数据.
更新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>
在更新的查询中,MAX(sm)返回26,然后在条件WHERE sm> MAX(sm)-16表示返回“sm”值大于26 -16即10的所有行.您可以使用替换变量在运行时输入值16.
