查看: 4641|回复: 15

[PL/SQL] 【大话IT】请教Oracle分析函数,请大虾求助!

[复制链接]
论坛徽章:
3
优秀写手
日期:2015-03-04 06:00:142015年新春福章
日期:2015-03-04 14:53:162015年新春福章
日期:2015-03-06 11:58:39
发表于 2015-2-16 08:50 | 显示全部楼层 |阅读模式
需要实现以下功能:

表一:
客户   物料                    数量
001   20152621351       1000

表二:
客户   物料                    数量          到期日期
001   20152621351       200           2015-1-1

001   20152621351       500           2015-5-1
001   20152621351       500           2015-3-1

表一和表二能自动根据条件(根据客户、物料)来匹配数量,按照到期日期的降序排序匹配。

最终显示:
表二:
客户   物料                    数量          到期日期        表一对应的数量
001   20152621351       200           2015-1-1        200

001   20152621351       500           2015-5-1        300
001   20152621351       500           2015-3-1        500



请教,有什么分析函数可以实现这个功能!

环境:Oracle9i


论坛徽章:
127
ITPUB元老
日期:2007-07-04 17:27:50会员2007贡献徽章
日期:2007-09-26 18:42:10现任管理团队成员
日期:2011-05-07 01:45:08优秀写手
日期:2015-01-09 06:00:14版主7段
日期:2015-07-16 02:10:00
发表于 2015-2-16 12:29 | 显示全部楼层
本帖最后由 bell6248 于 2015-2-16 15:38 编辑



代码如下:

我认为LZ给的结果是有问题的,应该是如下才合理,其实"
表一对应的数量"应该是实际消耗的数量

SQL> with t1 as
  2  (
  3   select '001' cust_no, '20152621351' material_no, 1000 qty from dual
  4  ),
  5  t2 as
  6  (
  7   select '001' cust_no, '20152621351' material_no, 450 qty, to_date('2015-1-1', 'yyyy-mm-dd') expired_date from dual
  8   union all
  9   select '001' cust_no, '20152621351' material_no, 623 qty, to_date('2015-5-1', 'yyyy-mm-dd') expired_date from dual
10   union all
11   select '001' cust_no, '20152621351' material_no, 500 qty, to_date('2015-3-1', 'yyyy-mm-dd') expired_date from dual
12   union all
13   select '001' cust_no, '20152621351' material_no, 200 qty, to_date('2015-8-1', 'yyyy-mm-dd') expired_date from dual
14  )
15  select t2.cust_no "客户",
16         t2.material_no "物料",
17         t2.qty "数量",
18         t2.expired_date "到期日期",
19         case
20            when sum(t2.qty) over(order by t2.expired_date) - t2.qty <= t1.qty then
21                 t2.qty - greatest(sum(t2.qty) over(order by t2.expired_date) - t1.qty, 0)
22            else
23                 0
24         end "表一对应的数量"
25    from t1, t2
26   where t1.cust_no = t2.cust_no
27     and t1.material_no = t2.material_no
28  /

客户 物料              数量 到期日期    表一对应的数量
---- ----------- ---------- ----------- --------------
001  20152621351        450 2015/1/1               450
001  20152621351        500 2015/3/1               500
001  20152621351        623 2015/5/1                50
001  20152621351        200 2015/8/1                 0

SQL>

SQL>




使用道具 举报

回复
认证徽章
论坛徽章:
40
2014年新春福章
日期:2014-02-18 16:43:09喜羊羊
日期:2015-05-18 16:24:25慢羊羊
日期:2015-06-12 13:08:22暖羊羊
日期:2015-07-02 16:06:20暖羊羊
日期:2015-07-06 16:28:55狮子座
日期:2015-07-29 17:14:43摩羯座
日期:2015-09-02 13:58:47白羊座
日期:2015-09-08 10:39:06天枰座
日期:2015-09-17 21:41:53摩羯座
日期:2015-10-29 21:07:02
发表于 2015-2-16 14:25 | 显示全部楼层
bell6248 发表于 2015-2-16 12:29
代码如下:

SQL> with t1 as

思路和方法学习了。

测试中发现,应将else后的 t1.qty改为 greatest(t1.qty-sum(t2.qty) over(order by t2.expired_date) ,0),否则当表二中的累计数量大于T1表中的对应物料数量时,会把t1表中的全部物料重复分配。
例如表二中的对应数量为200的那条记录,如果其值改为1200,则结果出现三条记录分配的数量均为1000.

使用道具 举报

回复
求职 : 数据库管理员
认证徽章
论坛徽章:
11
ITPUB社区千里马徽章
日期:2013-06-09 10:15:34懒羊羊
日期:2015-03-04 14:52:11懒羊羊
日期:2015-02-10 13:36:05马上有对象
日期:2015-02-02 12:29:02红宝石
日期:2015-01-19 09:44:10马上有车
日期:2014-11-11 14:16:07马上有车
日期:2014-03-27 15:59:39优秀写手
日期:2014-03-12 06:00:13马上有钱
日期:2014-02-18 16:43:092014年新春福章
日期:2014-02-18 16:43:09
发表于 2015-2-16 14:41 | 显示全部楼层
我还没看懂需求

使用道具 举报

回复
论坛徽章:
127
ITPUB元老
日期:2007-07-04 17:27:50会员2007贡献徽章
日期:2007-09-26 18:42:10现任管理团队成员
日期:2011-05-07 01:45:08优秀写手
日期:2015-01-09 06:00:14版主7段
日期:2015-07-16 02:10:00
发表于 2015-2-16 14:48 | 显示全部楼层
本帖最后由 bell6248 于 2015-2-16 15:39 编辑

请参考2#,LZ给的结果我认为不合理

使用道具 举报

回复
论坛徽章:
3
优秀写手
日期:2015-03-04 06:00:142015年新春福章
日期:2015-03-04 14:53:162015年新春福章
日期:2015-03-06 11:58:39
 楼主| 发表于 2015-2-16 15:56 | 显示全部楼层
bell6248 发表于 2015-2-16 12:29
代码如下:

我认为LZ给的结果是有问题的,应该是如下才合理,其实"表一对应的数量"应该是实际消耗的 ...

谢谢您的回复,我刚也测试了一下,现在如果扣除的时候,有余数,怎么来显示,谢谢!
with t1 as
    (
     select '001' cust_no, '20152621351' material_no, 1000 qty from dual
    ),
    t2 as
    (
     select '001' cust_no, '20152621351' material_no, 450 qty, to_date('2015-1-1', 'yyyy-mm-dd') expired_date from dual
     union all
     select '001' cust_no, '20152621351' material_no, 623 qty, to_date('2015-5-1', 'yyyy-mm-dd') expired_date from dual
     union all
     select '001' cust_no, '20152621351' material_no, 200 qty, to_date('2015-5-1', 'yyyy-mm-dd') expired_date from dual
   union all
   select '001' cust_no, '20152621351' material_no, 500 qty, to_date('2015-3-1', 'yyyy-mm-dd') expired_date from dual
   union all
   select '001' cust_no, '20152621351' material_no, 200 qty, to_date('2015-8-1', 'yyyy-mm-dd') expired_date from dual
   union all
   select '001' cust_no, '20152621353' material_no, 2000 qty, to_date('2015-8-1', 'yyyy-mm-dd') expired_date from dual
  )
  select t2.cust_no "客户",
        t2.material_no "物料",
        t2.qty "数量",
        t2.expired_date "到期日期",
        case
           when sum(t2.qty) over(order by t2.expired_date) - t2.qty <= t1.qty then
                 t2.qty - greatest(sum(t2.qty) over(order by t2.expired_date) - t1.qty, 0)
            else
                 0
        end "表一对应的数量"
   from t1, t2
  where t1.cust_no(+) = t2.cust_no
    and t1.material_no(+) = t2.material_no

使用道具 举报

回复
论坛徽章:
3
优秀写手
日期:2015-03-04 06:00:142015年新春福章
日期:2015-03-04 14:53:162015年新春福章
日期:2015-03-06 11:58:39
 楼主| 发表于 2015-2-16 15:57 | 显示全部楼层
谢谢bell6248,我已经解决了

使用道具 举报

回复
认证徽章
论坛徽章:
40
2014年新春福章
日期:2014-02-18 16:43:09喜羊羊
日期:2015-05-18 16:24:25慢羊羊
日期:2015-06-12 13:08:22暖羊羊
日期:2015-07-02 16:06:20暖羊羊
日期:2015-07-06 16:28:55狮子座
日期:2015-07-29 17:14:43摩羯座
日期:2015-09-02 13:58:47白羊座
日期:2015-09-08 10:39:06天枰座
日期:2015-09-17 21:41:53摩羯座
日期:2015-10-29 21:07:02
发表于 2015-2-16 16:12 | 显示全部楼层
bell6248 发表于 2015-2-16 14:48
请参考2#,LZ给的结果我认为不合理

我的理解是“表一对应的数量”列中的累计值,不会超过表一中的对应物料的数量,本例中,就是不应该超过1000。
换句话说,是要把表1的物料分配给各个使用该物料的用户,但总量只有这么多,不能多分。

使用道具 举报

回复
论坛徽章:
127
ITPUB元老
日期:2007-07-04 17:27:50会员2007贡献徽章
日期:2007-09-26 18:42:10现任管理团队成员
日期:2011-05-07 01:45:08优秀写手
日期:2015-01-09 06:00:14版主7段
日期:2015-07-16 02:10:00
发表于 2015-2-16 16:14 | 显示全部楼层
DLLJW123 发表于 2015-2-16 15:56
谢谢您的回复,我刚也测试了一下,现在如果扣除的时候,有余数,怎么来显示,谢谢!
with t1 as
    (
...



测试一下吧!

SQL> with t1 as
  2      (
  3       select '001' cust_no, '20152621351' material_no, 1000 qty from dual
  4      ),
  5      t2 as
  6      (
  7       select '001' cust_no, '20152621351' material_no, 450 qty, to_date('2015-1-1', 'yyyy-mm-dd') expired_date from dual
  8       union all
  9       select '001' cust_no, '20152621351' material_no, 623 qty, to_date('2015-5-1', 'yyyy-mm-dd') expired_date from dual
10       union all
11       select '001' cust_no, '20152621351' material_no, 200 qty, to_date('2015-5-1', 'yyyy-mm-dd') expired_date from dual
12     union all
13     select '001' cust_no, '20152621351' material_no, 500 qty, to_date('2015-3-1', 'yyyy-mm-dd') expired_date from dual
14     union all
15     select '001' cust_no, '20152621351' material_no, 200 qty, to_date('2015-8-1', 'yyyy-mm-dd') expired_date from dual
16     union all
17     select '001' cust_no, '20152621353' material_no, 2000 qty, to_date('2015-8-1', 'yyyy-mm-dd') expired_date from dual
18    )
19  select t2.cust_no "客户",
20         t2.material_no "物料",
21         t2.qty "t2_qty 数量",
22         t2.expired_date "到期日期",
23         case
24            when sum(t2.qty) over(partition by t2.cust_no, t2.material_no order by t2.expired_date, rownum) - t2.qty <= t1.qty then
25                 t2.qty - greatest(sum(t2.qty) over(partition by t2.cust_no, t2.material_no order by t2.expired_date, rownum) - t1.qty, 0)
26            else
27                 0
28         end "表一对应的消耗数量"
29    from t1, t2
30   where t1.cust_no(+) = t2.cust_no
31     and t1.material_no(+) = t2.material_no
32  /

客户 物料        t2_qty 数量 到期日期    表一对应的消耗数量
---- ----------- ----------- ----------- ------------------
001  20152621351         450 2015/1/1                   450
001  20152621351         500 2015/3/1                   500
001  20152621351         200 2015/5/1                    50
001  20152621351         623 2015/5/1                     0
001  20152621351         200 2015/8/1                     0
001  20152621353        2000 2015/8/1                     0

6 rows selected

SQL>



使用道具 举报

回复
论坛徽章:
401
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2015-2-16 20:57 | 显示全部楼层
lz怎么知道分析函数能解决?

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表