Oracle排序函數Rank
2024-08-30 23:15:06
出口給報關行出貨的時候,同一票shipment中合併多個invoice跟packing,轉出到廠商的報關系統時候,出口報關的序號會將invoice的序號做自動增加。
因為wafer會有出口給其他外包做初階加工,加工完之後再回貨,回貨就是需要再進口回來。進口做報關的時候就會有原出口的報單號碼,也需要明確到報單的項次。
因為原本出口并沒有字段記錄對於的出口報關項次,所以只能重新去計算一遍。
舉例如下,SI# 100600486為兩筆invoice並單出貨,第一筆INV#100614-03-CSS有30個項次,第二筆INV#100615-01-CSS有11個項次。
SELECT M.SHIPMENT_ID, I.INV_NO, I.SEQ_NO, I.WIP_ENTITY_NAME,I.DESCP
FROM DECL_INV M, DECL_INV_ITEM I
WHERE M.INV_NO = I.INV_NO
AND M.DOC_FLAG = 'D'
AND I.DOC_FLAG = 'D'
AND M.SHIPMENT_ID = ''
SHIPMENT_ID | INV_NO | SEQ_NO | WIP_ENTITY_NAME | DESCP |
---|---|---|---|---|
100600486 | 100614-03-CSS | 1 | 227470 | 6" Wafer |
100600486 | 100614-03-CSS | 2 | 227471 | 6" Wafer |
100600486 | 100614-03-CSS | 3 | 227472 | 6" Wafer |
100600486 | 100614-03-CSS | 4 | 227473 | 6" Wafer |
100600486 | 100614-03-CSS | 5 | 227474 | 6" Wafer |
100600486 | 100614-03-CSS | 6 | 227475 | 6" Wafer |
100600486 | 100614-03-CSS | 7 | 227476 | 6" Wafer |
100600486 | 100614-03-CSS | 8 | 227477 | 6" Wafer |
100600486 | 100614-03-CSS | 9 | 227478 | 6" Wafer |
100600486 | 100614-03-CSS | 10 | 227479 | 6" Wafer |
100600486 | 100614-03-CSS | 11 | 227480 | 6" Wafer |
100600486 | 100614-03-CSS | 12 | 227481 | 6" Wafer |
100600486 | 100614-03-CSS | 13 | 227482 | 6" Wafer |
100600486 | 100614-03-CSS | 14 | 227484 | 6" Wafer |
100600486 | 100614-03-CSS | 15 | 227485 | 6" Wafer |
100600486 | 100614-03-CSS | 16 | 227487 | 6" Wafer |
100600486 | 100614-03-CSS | 17 | 227488 | 6" Wafer |
100600486 | 100614-03-CSS | 18 | 227491 | 6" Wafer |
100600486 | 100614-03-CSS | 19 | 227493 | 6" Wafer |
100600486 | 100614-03-CSS | 20 | 227495 | 6" Wafer |
100600486 | 100614-03-CSS | 21 | 227496 | 6" Wafer |
100600486 | 100614-03-CSS | 22 | 227497 | 6" Wafer |
100600486 | 100614-03-CSS | 23 | 227498 | 6" Wafer |
100600486 | 100614-03-CSS | 24 | 227499 | 6" Wafer |
100600486 | 100614-03-CSS | 25 | 227500 | 8" Wafer |
100600486 | 100614-03-CSS | 26 | 227501 | 8" Wafer |
100600486 | 100614-03-CSS | 27 | 227502 | 6" Wafer |
100600486 | 100614-03-CSS | 28 | 227503 | 5" Wafer |
100600486 | 100614-03-CSS | 29 | 227504 | 5" Wafer |
100600486 | 100614-03-CSS | 30 | 227505 | 5" Wafer |
100600486 | 100615-01-CSS | 1 | 227748 | 6" Wafer |
100600486 | 100615-01-CSS | 2 | 227753 | 6" Wafer |
100600486 | 100615-01-CSS | 3 | 227754 | 6" Wafer |
100600486 | 100615-01-CSS | 4 | 227756 | 6" Wafer |
100600486 | 100615-01-CSS | 5 | 227757 | 6" Wafer |
100600486 | 100615-01-CSS | 6 | 227758 | 6" Wafer |
100600486 | 100615-01-CSS | 7 | 227759 | 6" Wafer |
100600486 | 100615-01-CSS | 8 | 227761 | 6" Wafer |
100600486 | 100615-01-CSS | 9 | 227762 | 6" Wafer |
100600486 | 100615-01-CSS | 10 | 227765 | 5" Wafer |
100600486 | 100615-01-CSS | 11 | 227766 | 6" Wafer |
SELECT rank() over(partition by M.SHIPMENT_ID ORDER BY I.INV_NO ASC, I.SEQ_NO ASC) DECLEAR_SEQ_NO,
M.SHIPMENT_ID,
I.INV_NO,
I.SEQ_NO,
I.WIP_ENTITY_NAME,
I.DESCP
FROM DECL_INV M, DECL_INV_ITEM I
WHERE M.INV_NO = I.INV_NO
AND M.DOC_FLAG = 'D'
AND I.DOC_FLAG = 'D'
AND M.SHIPMENT_ID = ''
這裡其實主要是用到RANK函數,依據INV NO跟SEQ NO來做排序得到一個排序之後的序號,結果如下:
DECLEAR_SEQ_NO | SHIPMENT_ID | INV_NO | SEQ_NO | WIP_ENTITY_NAME | DESCP |
---|---|---|---|---|---|
100600486 | 100614-03-CSS | 1 | 227470 | 6" Wafer | |
100600486 | 100614-03-CSS | 2 | 227471 | 6" Wafer | |
100600486 | 100614-03-CSS | 3 | 227472 | 6" Wafer | |
100600486 | 100614-03-CSS | 4 | 227473 | 6" Wafer | |
100600486 | 100614-03-CSS | 5 | 227474 | 6" Wafer | |
100600486 | 100614-03-CSS | 6 | 227475 | 6" Wafer | |
100600486 | 100614-03-CSS | 7 | 227476 | 6" Wafer | |
100600486 | 100614-03-CSS | 8 | 227477 | 6" Wafer | |
100600486 | 100614-03-CSS | 9 | 227478 | 6" Wafer | |
100600486 | 100614-03-CSS | 10 | 227479 | 6" Wafer | |
100600486 | 100614-03-CSS | 11 | 227480 | 6" Wafer | |
100600486 | 100614-03-CSS | 12 | 227481 | 6" Wafer | |
100600486 | 100614-03-CSS | 13 | 227482 | 6" Wafer | |
100600486 | 100614-03-CSS | 14 | 227484 | 6" Wafer | |
100600486 | 100614-03-CSS | 15 | 227485 | 6" Wafer | |
100600486 | 100614-03-CSS | 16 | 227487 | 6" Wafer | |
100600486 | 100614-03-CSS | 17 | 227488 | 6" Wafer | |
100600486 | 100614-03-CSS | 18 | 227491 | 6" Wafer | |
100600486 | 100614-03-CSS | 19 | 227493 | 6" Wafer | |
100600486 | 100614-03-CSS | 20 | 227495 | 6" Wafer | |
100600486 | 100614-03-CSS | 21 | 227496 | 6" Wafer | |
100600486 | 100614-03-CSS | 22 | 227497 | 6" Wafer | |
100600486 | 100614-03-CSS | 23 | 227498 | 6" Wafer | |
100600486 | 100614-03-CSS | 24 | 227499 | 6" Wafer | |
100600486 | 100614-03-CSS | 25 | 227500 | 8" Wafer | |
100600486 | 100614-03-CSS | 26 | 227501 | 8" Wafer | |
100600486 | 100614-03-CSS | 27 | 227502 | 6" Wafer | |
100600486 | 100614-03-CSS | 28 | 227503 | 5" Wafer | |
100600486 | 100614-03-CSS | 29 | 227504 | 5" Wafer | |
100600486 | 100614-03-CSS | 30 | 227505 | 5" Wafer | |
100600486 | 100615-01-CSS | 1 | 227748 | 6" Wafer | |
100600486 | 100615-01-CSS | 2 | 227753 | 6" Wafer | |
100600486 | 100615-01-CSS | 3 | 227754 | 6" Wafer | |
100600486 | 100615-01-CSS | 4 | 227756 | 6" Wafer | |
100600486 | 100615-01-CSS | 5 | 227757 | 6" Wafer | |
100600486 | 100615-01-CSS | 6 | 227758 | 6" Wafer | |
100600486 | 100615-01-CSS | 7 | 227759 | 6" Wafer | |
100600486 | 100615-01-CSS | 8 | 227761 | 6" Wafer | |
100600486 | 100615-01-CSS | 9 | 227762 | 6" Wafer | |
100600486 | 100615-01-CSS | 10 | 227765 | 5" Wafer | |
100600486 | 100615-01-CSS | 11 | 227766 | 6" Wafer |
最新文章
- html中role的作用
- MyISAM 和InnoDB 的区别.(存储,索引, 事务, 锁)
- 总结/PSP初体验—排球计分程序1.0
- Xcode添加摄像机访问权限<;转>;
- JavaScript中{}+{}
- C#实现类似";hello $world";的格式化字符串方法
- CSS样式案例(2)-制作一个简单的登录界面
- 【Leetcode】【Medium】Decode Ways
- MSCRM 迁移 数据库 服务器
- Android Toast 设置到屏幕中间,自定义Toast的实现方法,及其说明
- 定时生成bat命令
- UVA 12219 Common Subexpression Elimination
- drawInRect:withAttributes:
- iOS 多线程开发之OperationQueue(二)NSOperation VS GCD
- Docker资源网站收藏
- Servlet中service()方法
- vue手把手教你实现走马灯商品左右轮播图
- .NET NPOI操作Excel 让单元格的内容换行
- 说明split()与join()函数的区别?
- nginx 优化(突破十万并发)