為了將復(fù)雜的sql 給非技術(shù)部門的人眼使用睬罗,可以通過創(chuàng)建視圖的方法將復(fù)雜的sql簡單化冀瓦。
Oracle對(duì)象教程:視圖(View)創(chuàng)建使用默勾,視圖(View)實(shí)際上是一張或者多張表上的預(yù)定義查詢番电,這些表稱為基表袍镀。從視圖中查詢信息與從表中查詢信息的方法完全相同默蚌。只需要簡單的SELECT…FROM即可。
視圖具有以下優(yōu)點(diǎn):
1. 可以限制用戶只能通過視圖檢索數(shù)據(jù)苇羡。這樣就可以對(duì)最終用戶屏蔽建表時(shí)底層的基表绸吸。
2. 可以將復(fù)雜的查詢保存為視圖∩杞可以對(duì)最終用戶屏蔽一定的復(fù)雜性锦茁。
3. 限制某個(gè)視圖只能訪問基表中的部分列或者部分行的特定數(shù)據(jù)。這樣可以實(shí)現(xiàn)一定的安全性叉存。
4. 從多張基表中按一定的業(yè)務(wù)邏輯抽出用戶關(guān)心的部分码俩,形成一張?zhí)摂M表。
語法結(jié)構(gòu):創(chuàng)建視圖
CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view_name
AS
SELECT查詢
[WITH READ ONLY CONSTRAINT]
語法解析:
1. OR REPLACE:如果視圖已經(jīng)存在歼捏,則替換舊視圖稿存。
2. FORCE:即使基表不存在,也可以創(chuàng)建該視圖瞳秽,但是該視圖不能正常使用瓣履,當(dāng)基表創(chuàng)建成功后,視圖才能正常使用寂诱。
3. NOFORCE:如果基表不存在拂苹,無法創(chuàng)建視圖,該項(xiàng)是默認(rèn)選項(xiàng)。
4. WITH READ ONLY:默認(rèn)可以通過視圖對(duì)基表執(zhí)行增刪改操作瓢棒,但是有很多在基表上的限制(比如:基表中某列不能為空浴韭,但是該列沒有出現(xiàn)在視圖中,則不能通過視圖執(zhí)行insert操作)脯宿,WITH READ ONLY說明視圖是只讀視圖念颈,不能通過該視圖進(jìn)行增刪改操作。現(xiàn)實(shí)開發(fā)中连霉,基本上不通過視圖對(duì)表中的數(shù)據(jù)進(jìn)行增刪改操作榴芳。
案例3:基于EMP表和DEPT表創(chuàng)建視圖
原查詢sql:
WITH liugang_01 AS
? (select T.DATA_SOURCE,
? ? ? ? ? T.WORK_ORDER_NO,
? ? ? ? ? T.WINIT_ORDER_NO,
? ? ? ? ? T.HOLDING_LOCATOR_SERNO,
? ? ? ? ? T.PICKING_SUBAREA_NAME,
? ? ? ? ? T.HOLDING_LOCATOR_NO,
? ? ? ? ? T.PICKING_TASK_NO,
? ? ? ? ? T.ITEM_SERNO,
? ? ? ? ? T.MERCHANDISE_SERNO,
? ? ? ? ? T.UPDATED,
? ? ? ? ? ROW_NUMBER() OVER(PARTITION BY T.DATA_SOURCE, T.PICKING_TASK_NO ORDER BY T.UPDATED) RN
? ? ? from XX_xx.XXXXXXXXXXXt
? ? where T.IS_ACTIVE = 'Y'
? ? ? AND T.IS_DELETE = 'N'
? ? ? AND T.UPDATEDBY NOT LIKE 'winittest%'
? ? ? AND T.UPDATED >= TRUNC(SYSDATE, 'MM'))
? SELECT A.DATA_SOURCE,
? ? ? ? A.PICKING_DATE,
? ? ? ? A.PRODUCT_CLASS,
? ? ? ? A.SI_COUNT,
? ? ? ? A.ACT_CONSUM_HOUR,
? ? ? ? FZ.揀選輔助工時(shí),
? ? ? ? R.SPLIT_RATIO 輔助工時(shí)分?jǐn)偙壤?
? ? ? ? A.SI_COUNT / A.ACT_CONSUM_HOUR 效率_不含輔助,
? ? ? ? A.SI_COUNT /
? ? ? ? (A.ACT_CONSUM_HOUR + NVL(FZ.揀選輔助工時(shí), 0) * NVL(R.SPLIT_RATIO, 0)) 效率_含輔助
? ? FROM ( -- 標(biāo)準(zhǔn)任務(wù)揀選數(shù)量、耗時(shí)
? ? ? ? ? SELECT DATA_SOURCE,
? ? ? ? ? ? ? ? ? TRUNC(UPDATED, 'DD') PICKING_DATE,
? ? ? ? ? ? ? ? ? PRODUCT_CLASS,
? ? ? ? ? ? ? ? ? COUNT(ITEM_SERNO) SI_COUNT,
? ? ? ? ? ? ? ? ? SUM(ACT_CONSUM_HOUR) ACT_CONSUM_HOUR
? ? ? ? ? ? FROM ( -- 計(jì)算每個(gè)單品的揀選工時(shí)
? ? ? ? ? ? ? ? ? SELECT J.DATA_SOURCE,
? ? ? ? ? ? ? ? ? ? ? ? ? J.HOLDING_LOCATOR_SERNO,
? ? ? ? ? ? ? ? ? ? ? ? ? J.PICKING_SUBAREA_NAME,
? ? ? ? ? ? ? ? ? ? ? ? ? J.PICKING_TASK_NO,
? ? ? ? ? ? ? ? ? ? ? ? ? J.ITEM_SERNO,
? ? ? ? ? ? ? ? ? ? ? ? ? P.PRODUCT_CLASS,
? ? ? ? ? ? ? ? ? ? ? ? ? J.UPDATED,
? ? ? ? ? ? ? ? ? ? ? ? ? (J.UPDATED - J2.UPDATED) * 24 ACT_CONSUM_HOUR,
? ? ? ? ? ? ? ? ? ? ? ? ? J.RN
? ? ? ? ? ? ? ? ? ? FROM XXXX J
? ? ? ? ? ? ? ? ? ? LEFT JOIN xxxxx.xxxxP
? ? ? ? ? ? ? ? ? ? ? ON P.PRODUCT_CODE = J.MERCHANDISE_SERNO
? ? ? ? ? ? ? ? ? ? LEFT JOIN xxxx J2
? ? ? ? ? ? ? ? ? ? ? ON J2.DATA_SOURCE = J.DATA_SOURCE
? ? ? ? ? ? ? ? ? ? ? AND J2.PICKING_TASK_NO = J.PICKING_TASK_NO
? ? ? ? ? ? ? ? ? ? ? AND J2.RN = J.RN - 1
? ? ? ? ? ? ? ? ? ? WHERE J.RN >= 2
? ? ? ? ? ? ? ? ? ? ? AND J.PICKING_TASK_NO IS NOT NULL
? ? ? ? ? ? ? ? ? ? ? and J.UPDATED - J2.UPDATED < 0.021 --剔除任務(wù)間隔超過半小時(shí)的數(shù)據(jù)
? ? ? ? ? ? ? ? ? )
? ? ? ? ? WHERE UPDATED < TRUNC(SYSDATE, 'DD')
? ? ? ? ? ? ? ? --? AND ACT_CONSUM_HOUR>0
? ? ? ? ? ? and DATA_SOURCE NOT IN ('CWM_USTX', 'CWM_UKBH', 'CWM_BEMO')
? ? ? ? ? GROUP BY DATA_SOURCE, TRUNC(UPDATED, 'DD'), PRODUCT_CLASS) A
? ? LEFT JOIN ( -- 待分?jǐn)偟膾x輔助工時(shí)
? ? ? ? ? ? ? SELECT TRUNC(C.FINISHED_DATE, 'DD') FINISH_DATE,
? ? ? ? ? ? ? ? ? ? ? WH.WAREHOUSE_CODE_OW,
? ? ? ? ? ? ? ? ? ? ? C.SU_CODE,
? ? ? ? ? ? ? ? ? ? ? W.SU_NAME,
? ? ? ? ? ? ? ? ? ? ? SUM(C.ACTUAL_WORKTIME) 揀選輔助工時(shí)
? ? ? ? ? ? ? ? FROM xxxx.cccccc C
? ? ? ? ? ? ? ? left join xxx .xxxxx WH
? ? ? ? ? ? ? ? ? on WH.WAREHOUSE_VALUE = C.WAREHOUSE_CODE
? ? ? ? ? ? ? ? LEFT JOIN BI_Dw.D_T_SU W
? ? ? ? ? ? ? ? ? ON W.SU_CODE = C.SU_CODE
? ? ? ? ? ? ? ? WHERE C.FINISHED_DATE >= TRUNC(SYSDATE, 'MM')
? ? ? ? ? ? ? ? ? AND C.FINISHED_DATE < TRUNC(SYSDATE, 'DD')
? ? ? ? ? ? ? ? ? AND W.SU_NAME = '訂單揀選'
? ? ? ? ? ? ? ? ? AND C.WORKTIME_TYPE = '輔助工時(shí)'
? ? ? ? ? ? ? ? ? and C.ACTUAL_WORKTIME > 0
? ? ? ? ? ? ? ? GROUP BY TRUNC(C.FINISHED_DATE, 'DD'),
? ? ? ? ? ? ? ? ? ? ? ? ? WH.WAREHOUSE_CODE_OW,
? ? ? ? ? ? ? ? ? ? ? ? ? C.WORKTIME_TYPE,
? ? ? ? ? ? ? ? ? ? ? ? ? C.SU_CODE,
? ? ? ? ? ? ? ? ? ? ? ? ? W.SU_NAME) FZ
? ? ? ON FZ.WAREHOUSE_CODE_OW = A.DATA_SOURCE
? ? AND FZ.FINISH_DATE = A.PICKING_DATE
? ? LEFT JOIN ( -- 計(jì)算高位揀選量跺撼,以計(jì)算分?jǐn)偙壤?/p>
? ? ? ? ? ? ? SELECT DATA_SOURCE,
? ? ? ? ? ? ? ? ? ? ? PICKING_DATE,
? ? ? ? ? ? ? ? ? ? ? SUBAREA_SIGN,
? ? ? ? ? ? ? ? ? ? ? PRODUCT_CLASS,
? ? ? ? ? ? ? ? ? ? ? SI_COUNT,
? ? ? ? ? ? ? ? ? ? ? SUM(SI_COUNT) OVER(PARTITION BY DATA_SOURCE, PICKING_DATE) AS TOTAL_SICOUNT,
? ? ? ? ? ? ? ? ? ? ? SI_COUNT / SUM(SI_COUNT) OVER(PARTITION BY DATA_SOURCE, PICKING_DATE) SPLIT_RATIO
? ? ? ? ? ? ? ? FROM (SELECT T.DATA_SOURCE,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? TRUNC(T.UPDATED, 'DD') PICKING_DATE,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? L.SUBAREA_SIGN,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? P.PRODUCT_CLASS,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? COUNT(T.ITEM_SERNO) SI_COUNT
? ? ? ? ? ? ? ? ? ? ? ? ? FROM xxxxxx T
? ? ? ? ? ? ? ? ? ? ? ? ? LEFT xxxx.xxxxx WH
? ? ? ? ? ? ? ? ? ? ? ? ? ? ON WH.WAREHOUSE_CODE_OW = T.DATA_SOURCE
? ? ? ? ? ? ? ? ? ? ? ? ? LEFT JOIN xxx.qqqqq L
? ? ? ? ? ? ? ? ? ? ? ? ? ? ON L.WAREHOUSE_CODE = WH.WAREHOUSE_VALUE
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? --? AND L.SUBAREA_NAME=T.PICKING_SUBAREA_NAME
? ? ? ? ? ? ? ? ? ? ? ? ? AND L.LOCATOR_SERNO = T.HOLDING_LOCATOR_SERNO
? ? ? ? ? ? ? ? ? ? ? ? ? AND L.IS_ACTIVE = 'Y'
? ? ? ? ? ? ? ? ? ? ? ? ? AND L.IS_DELETE = 'N'
? ? ? ? ? ? ? ? ? ? ? ? ? LEFT JOIN xxxx .xxxxxx P
? ? ? ? ? ? ? ? ? ? ? ? ? ? ON P.PRODUCT_CODE = T.MERCHANDISE_SERNO
? ? ? ? ? ? ? ? ? ? ? ? WHERE L.SUBAREA_SIGN = '存儲(chǔ)庫位'
? ? ? ? ? ? ? ? ? ? ? ? ? AND T.ITEM_SERNO IS NOT NULL -- 異常揀選情況下單品條碼為空
? ? ? ? ? ? ? ? ? ? ? ? GROUP BY T.DATA_SOURCE,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? TRUNC(T.UPDATED, 'DD'),
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? L.SUBAREA_SIGN,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? P.PRODUCT_CLASS)) R
? ? ? ON R.DATA_SOURCE = A.DATA_SOURCE
? ? AND R.PICKING_DATE = A.PICKING_DATE
? ? AND R.PRODUCT_CLASS = A.PRODUCT_CLASS
? ORDER BY A.DATA_SOURCE, A.PICKING_DATE, A.PRODUCT_CLASS
此SQL 用了臨時(shí)表窟感,在創(chuàng)建視圖的時(shí)候需要先把這個(gè)sql的結(jié)果集取到之后才能創(chuàng)建視圖,那么就需要在這個(gè)sql外面在包一層 select * from ( 歉井。柿祈。查詢的SQL。哩至。)躏嚎;視圖創(chuàng)建如下:
create view bi_wdddx.qssssuery_efficiency as select * from (
? WITH ssss? AS
? (select T.DATA_SOURCE,
? ? ? ? ? T.WORK_ORDER_NO,
? ? ? ? ? T.WINIT_ORDER_NO,
? ? ? ? ? T.HOLDING_LOCATOR_SERNO,
? ? ? ? ? T.PICKING_SUBAREA_NAME,
? ? ? ? ? T.HOLDING_LOCATOR_NO,
? ? ? ? ? T.PICKING_TASK_NO,
? ? ? ? ? T.ITEM_SERNO,
? ? ? ? ? T.MERCHANDISE_SERNO,
? ? ? ? ? T.UPDATED,
? ? ? ? ? ROW_NUMBER() OVER(PARTITION BY T.DATA_SOURCE, T.PICKING_TASK_NO ORDER BY T.UPDATED) RN
? ? ? from xxxx.sssssdf t
? ? where T.IS_ACTIVE = 'Y'
? ? ? AND T.IS_DELETE = 'N'
? ? ? AND T.UPDATEDBY NOT LIKE 'winittest%'
? ? ? AND T.UPDATED >= TRUNC(SYSDATE, 'MM'))
? SELECT A.DATA_SOURCE,
? ? ? ? A.PICKING_DATE,
? ? ? ? A.PRODUCT_CLASS,
? ? ? ? A.SI_COUNT,
? ? ? ? A.ACT_CONSUM_HOUR,
? ? ? ? FZ.揀選輔助工時(shí),
? ? ? ? R.SPLIT_RATIO 輔助工時(shí)分?jǐn)偙壤?
? ? ? ? A.SI_COUNT / A.ACT_CONSUM_HOUR 效率_不含輔助,
? ? ? ? A.SI_COUNT /
? ? ? ? (A.ACT_CONSUM_HOUR + NVL(FZ.揀選輔助工時(shí), 0) * NVL(R.SPLIT_RATIO, 0)) 效率_含輔助
? ? FROM ( -- 標(biāo)準(zhǔn)任務(wù)揀選數(shù)量、耗時(shí)
? ? ? ? ? SELECT DATA_SOURCE,
? ? ? ? ? ? ? ? ? TRUNC(UPDATED, 'DD') PICKING_DATE,
? ? ? ? ? ? ? ? ? PRODUCT_CLASS,
? ? ? ? ? ? ? ? ? COUNT(ITEM_SERNO) SI_COUNT,
? ? ? ? ? ? ? ? ? SUM(ACT_CONSUM_HOUR) ACT_CONSUM_HOUR
? ? ? ? ? ? FROM ( -- 計(jì)算每個(gè)單品的揀選工時(shí)
? ? ? ? ? ? ? ? ? SELECT J.DATA_SOURCE,
? ? ? ? ? ? ? ? ? ? ? ? ? J.HOLDING_LOCATOR_SERNO,
? ? ? ? ? ? ? ? ? ? ? ? ? J.PICKING_SUBAREA_NAME,
? ? ? ? ? ? ? ? ? ? ? ? ? J.PICKING_TASK_NO,
? ? ? ? ? ? ? ? ? ? ? ? ? J.ITEM_SERNO,
? ? ? ? ? ? ? ? ? ? ? ? ? P.PRODUCT_CLASS,
? ? ? ? ? ? ? ? ? ? ? ? ? J.UPDATED,
? ? ? ? ? ? ? ? ? ? ? ? ? (J.UPDATED - J2.UPDATED) * 24 ACT_CONSUM_HOUR,
? ? ? ? ? ? ? ? ? ? ? ? ? J.RN
? ? ? ? ? ? ? ? ? ? FROM xxxxxx J
? ? ? ? ? ? ? ? ? ? LEFT JOIN xxxxx.ssssss P
? ? ? ? ? ? ? ? ? ? ? ON P.PRODUCT_CODE = J.MERCHANDISE_SERNO
? ? ? ? ? ? ? ? ? ? LEFT JOIN xxxxx J2
? ? ? ? ? ? ? ? ? ? ? ON J2.DATA_SOURCE = J.DATA_SOURCE
? ? ? ? ? ? ? ? ? ? ? AND J2.PICKING_TASK_NO = J.PICKING_TASK_NO
? ? ? ? ? ? ? ? ? ? ? AND J2.RN = J.RN - 1
? ? ? ? ? ? ? ? ? ? WHERE J.RN >= 2
? ? ? ? ? ? ? ? ? ? ? AND J.PICKING_TASK_NO IS NOT NULL
? ? ? ? ? ? ? ? ? ? ? and J.UPDATED - J2.UPDATED < 0.021 --剔除任務(wù)間隔超過半小時(shí)的數(shù)據(jù)
? ? ? ? ? ? ? ? ? )
? ? ? ? ? WHERE UPDATED < TRUNC(SYSDATE, 'DD')
? ? ? ? ? ? ? ? --? AND ACT_CONSUM_HOUR>0
? ? ? ? ? ? and DATA_SOURCE NOT IN ('CWM_USTX', 'CWM_UKBH', 'CWM_BEMO')
? ? ? ? ? GROUP BY DATA_SOURCE, TRUNC(UPDATED, 'DD'), PRODUCT_CLASS) A
? ? LEFT JOIN ( -- 待分?jǐn)偟膾x輔助工時(shí)
? ? ? ? ? ? ? SELECT TRUNC(C.FINISHED_DATE, 'DD') FINISH_DATE,
? ? ? ? ? ? ? ? ? ? ? WH.WAREHOUSE_CODE_OW,
? ? ? ? ? ? ? ? ? ? ? C.SU_CODE,
? ? ? ? ? ? ? ? ? ? ? W.SU_NAME,
? ? ? ? ? ? ? ? ? ? ? SUM(C.ACTUAL_WORKTIME) 揀選輔助工時(shí)
? ? ? ? ? ? ? ? FROM xxxx.sssss C
? ? ? ? ? ? ? ? left join xxxxx? WH
? ? ? ? ? ? ? ? ? on WH.WAREHOUSE_VALUE = C.WAREHOUSE_CODE
? ? ? ? ? ? ? ? LEFT JOIN sssss? W
? ? ? ? ? ? ? ? ? ON W.SU_CODE = C.SU_CODE
? ? ? ? ? ? ? ? WHERE C.FINISHED_DATE >= TRUNC(SYSDATE, 'MM')
? ? ? ? ? ? ? ? ? AND C.FINISHED_DATE < TRUNC(SYSDATE, 'DD')
? ? ? ? ? ? ? ? ? AND W.SU_NAME = '訂單揀選'
? ? ? ? ? ? ? ? ? AND C.WORKTIME_TYPE = '輔助工時(shí)'
? ? ? ? ? ? ? ? ? and C.ACTUAL_WORKTIME > 0
? ? ? ? ? ? ? ? GROUP BY TRUNC(C.FINISHED_DATE, 'DD'),
? ? ? ? ? ? ? ? ? ? ? ? ? WH.WAREHOUSE_CODE_OW,
? ? ? ? ? ? ? ? ? ? ? ? ? C.WORKTIME_TYPE,
? ? ? ? ? ? ? ? ? ? ? ? ? C.SU_CODE,
? ? ? ? ? ? ? ? ? ? ? ? ? W.SU_NAME) FZ
? ? ? ON FZ.WAREHOUSE_CODE_OW = A.DATA_SOURCE
? ? AND FZ.FINISH_DATE = A.PICKING_DATE
? ? LEFT JOIN ( -- 計(jì)算高位揀選量菩貌,以計(jì)算分?jǐn)偙壤?/p>
? ? ? ? ? ? ? SELECT DATA_SOURCE,
? ? ? ? ? ? ? ? ? ? ? PICKING_DATE,
? ? ? ? ? ? ? ? ? ? ? SUBAREA_SIGN,
? ? ? ? ? ? ? ? ? ? ? PRODUCT_CLASS,
? ? ? ? ? ? ? ? ? ? ? SI_COUNT,
? ? ? ? ? ? ? ? ? ? ? SUM(SI_COUNT) OVER(PARTITION BY DATA_SOURCE, PICKING_DATE) AS TOTAL_SICOUNT,
? ? ? ? ? ? ? ? ? ? ? SI_COUNT / SUM(SI_COUNT) OVER(PARTITION BY DATA_SOURCE, PICKING_DATE) SPLIT_RATIO
? ? ? ? ? ? ? ? FROM (SELECT T.DATA_SOURCE,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? TRUNC(T.UPDATED, 'DD') PICKING_DATE,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? L.SUBAREA_SIGN,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? P.PRODUCT_CLASS,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? COUNT(T.ITEM_SERNO) SI_COUNT
? ? ? ? ? ? ? ? ? ? ? ? ? FROM cccccT
? ? ? ? ? ? ? ? ? ? ? ? ? LEFT JOIN xcxcccx WH
? ? ? ? ? ? ? ? ? ? ? ? ? ? ON WH.WAREHOUSE_CODE_OW = T.DATA_SOURCE
? ? ? ? ? ? ? ? ? ? ? ? ? LEFT JOIN csacsacsa? L
? ? ? ? ? ? ? ? ? ? ? ? ? ? ON L.WAREHOUSE_CODE = WH.WAREHOUSE_VALUE
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? --? AND L.SUBAREA_NAME=T.PICKING_SUBAREA_NAME
? ? ? ? ? ? ? ? ? ? ? ? ? AND L.LOCATOR_SERNO = T.HOLDING_LOCATOR_SERNO
? ? ? ? ? ? ? ? ? ? ? ? ? AND L.IS_ACTIVE = 'Y'
? ? ? ? ? ? ? ? ? ? ? ? ? AND L.IS_DELETE = 'N'
? ? ? ? ? ? ? ? ? ? ? ? ? LEFT JOIN assadsadsa? P
? ? ? ? ? ? ? ? ? ? ? ? ? ? ON P.PRODUCT_CODE = T.MERCHANDISE_SERNO
? ? ? ? ? ? ? ? ? ? ? ? WHERE L.SUBAREA_SIGN = '存儲(chǔ)庫位'
? ? ? ? ? ? ? ? ? ? ? ? ? AND T.ITEM_SERNO IS NOT NULL -- 異常揀選情況下單品條碼為空
? ? ? ? ? ? ? ? ? ? ? ? GROUP BY T.DATA_SOURCE,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? TRUNC(T.UPDATED, 'DD'),
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? L.SUBAREA_SIGN,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? P.PRODUCT_CLASS)) R
? ? ? ON R.DATA_SOURCE = A.DATA_SOURCE
? ? AND R.PICKING_DATE = A.PICKING_DATE
? ? AND R.PRODUCT_CLASS = A.PRODUCT_CLASS
? ORDER BY A.DATA_SOURCE, A.PICKING_DATE, A.PRODUCT_CLASS)
可以設(shè)置條件的視圖:
CREATE OR REPLACE VIEW QUERY_EFFICIENCY AS
select "DATA_SOURCE","PICKING_DATE","PRODUCT_CLASS","SI_COUNT","ACT_CONSUM_HOUR","揀選輔助工時(shí)","輔助工時(shí)分?jǐn)偙壤?,"效率_不含輔助","效率_含輔助" from (
? WITH ssss AS
? (select T.DATA_SOURCE,
? ? ? ? ? T.WORK_ORDER_NO,
? ? ? ? ? T.WINIT_ORDER_NO,
? ? ? ? ? T.HOLDING_LOCATOR_SERNO,
? ? ? ? ? T.PICKING_SUBAREA_NAME,
? ? ? ? ? T.HOLDING_LOCATOR_NO,
? ? ? ? ? T.PICKING_TASK_NO,
? ? ? ? ? T.ITEM_SERNO,
? ? ? ? ? T.MERCHANDISE_SERNO,
? ? ? ? ? T.UPDATED,
? ? ? ? ? ROW_NUMBER() OVER(PARTITION BY T.DATA_SOURCE, T.PICKING_TASK_NO ORDER BY T.UPDATED) RN
? ? ? from fdsafdsgfds? t
? ? where T.IS_ACTIVE = 'Y'
? ? ? AND T.IS_DELETE = 'N'
? ? ? AND T.UPDATEDBY NOT LIKE 'winittest%'
? ? ? AND T.UPDATED >= TRUNC(SYSDATE, 'MM'))
? SELECT A.DATA_SOURCE,
? ? ? ? A.PICKING_DATE,
? ? ? ? A.PRODUCT_CLASS,
? ? ? ? A.SI_COUNT,
? ? ? ? A.ACT_CONSUM_HOUR,
? ? ? ? FZ.揀選輔助工時(shí),
? ? ? ? R.SPLIT_RATIO 輔助工時(shí)分?jǐn)偙壤?
? ? ? ? A.SI_COUNT / A.ACT_CONSUM_HOUR 效率_不含輔助,
? ? ? ? A.SI_COUNT /
? ? ? ? (A.ACT_CONSUM_HOUR + NVL(FZ.揀選輔助工時(shí), 0) * NVL(R.SPLIT_RATIO, 0)) 效率_含輔助
? ? FROM ( -- 標(biāo)準(zhǔn)任務(wù)揀選數(shù)量卢佣、耗時(shí)
? ? ? ? ? SELECT DATA_SOURCE,
? ? ? ? ? ? ? ? ? TRUNC(UPDATED, 'DD') PICKING_DATE,
? ? ? ? ? ? ? ? ? PRODUCT_CLASS,
? ? ? ? ? ? ? ? ? COUNT(ITEM_SERNO) SI_COUNT,
? ? ? ? ? ? ? ? ? SUM(ACT_CONSUM_HOUR) ACT_CONSUM_HOUR
? ? ? ? ? ? FROM ( -- 計(jì)算每個(gè)單品的揀選工時(shí)
? ? ? ? ? ? ? ? ? SELECT J.DATA_SOURCE,
? ? ? ? ? ? ? ? ? ? ? ? ? J.HOLDING_LOCATOR_SERNO,
? ? ? ? ? ? ? ? ? ? ? ? ? J.PICKING_SUBAREA_NAME,
? ? ? ? ? ? ? ? ? ? ? ? ? J.PICKING_TASK_NO,
? ? ? ? ? ? ? ? ? ? ? ? ? J.ITEM_SERNO,
? ? ? ? ? ? ? ? ? ? ? ? ? P.PRODUCT_CLASS,
? ? ? ? ? ? ? ? ? ? ? ? ? J.UPDATED,
? ? ? ? ? ? ? ? ? ? ? ? ? (J.UPDATED - J2.UPDATED) * 24 ACT_CONSUM_HOUR,
? ? ? ? ? ? ? ? ? ? ? ? ? J.RN
? ? ? ? ? ? ? ? ? ? FROM ssadsad J
? ? ? ? ? ? ? ? ? ? LEFT JOIN fdsfdsfds? P
? ? ? ? ? ? ? ? ? ? ? ON P.PRODUCT_CODE = J.MERCHANDISE_SERNO
? ? ? ? ? ? ? ? ? ? LEFT JOIN fddsfds J2
? ? ? ? ? ? ? ? ? ? ? ON J2.DATA_SOURCE = J.DATA_SOURCE
? ? ? ? ? ? ? ? ? ? ? AND J2.PICKING_TASK_NO = J.PICKING_TASK_NO
? ? ? ? ? ? ? ? ? ? ? AND J2.RN = J.RN - 1
? ? ? ? ? ? ? ? ? ? WHERE J.RN >= 2
? ? ? ? ? ? ? ? ? ? ? AND J.PICKING_TASK_NO IS NOT NULL
? ? ? ? ? ? ? ? ? ? ? and J.UPDATED - J2.UPDATED < 0.021 --剔除任務(wù)間隔超過半小時(shí)的數(shù)據(jù)
? ? ? ? ? ? ? ? ? )
? ? ? ? ? WHERE UPDATED < TRUNC(SYSDATE, 'DD')
? ? ? ? ? ? ? ? --? AND ACT_CONSUM_HOUR>0
? ? ? ? ? ? and DATA_SOURCE NOT IN ('CWM_USTX', 'CWM_UKBH', 'CWM_BEMO')
? ? ? ? ? GROUP BY DATA_SOURCE, TRUNC(UPDATED, 'DD'), PRODUCT_CLASS) A
? ? LEFT JOIN ( -- 待分?jǐn)偟膾x輔助工時(shí)
? ? ? ? ? ? ? SELECT TRUNC(C.FINISHED_DATE, 'DD') FINISH_DATE,
? ? ? ? ? ? ? ? ? ? ? WH.WAREHOUSE_CODE_OW,
? ? ? ? ? ? ? ? ? ? ? C.SU_CODE,
? ? ? ? ? ? ? ? ? ? ? W.SU_NAME,
? ? ? ? ? ? ? ? ? ? ? SUM(C.ACTUAL_WORKTIME) 揀選輔助工時(shí)
? ? ? ? ? ? ? ? FROM safdsafdsfds? C
? ? ? ? ? ? ? ? left join sdfdsfdsfdsf? WH
? ? ? ? ? ? ? ? ? on WH.WAREHOUSE_VALUE = C.WAREHOUSE_CODE
? ? ? ? ? ? ? ? LEFT JOIN BI_Dw.D_T_SU W
? ? ? ? ? ? ? ? ? ON W.SU_CODE = C.SU_CODE
? ? ? ? ? ? ? ? WHERE C.FINISHED_DATE >= TRUNC(SYSDATE, 'MM')
? ? ? ? ? ? ? ? ? AND C.FINISHED_DATE < TRUNC(SYSDATE, 'DD')
? ? ? ? ? ? ? ? ? AND W.SU_NAME = '訂單揀選'
? ? ? ? ? ? ? ? ? AND C.WORKTIME_TYPE = '輔助工時(shí)'
? ? ? ? ? ? ? ? ? and C.ACTUAL_WORKTIME > 0
? ? ? ? ? ? ? ? GROUP BY TRUNC(C.FINISHED_DATE, 'DD'),
? ? ? ? ? ? ? ? ? ? ? ? ? WH.WAREHOUSE_CODE_OW,
? ? ? ? ? ? ? ? ? ? ? ? ? C.WORKTIME_TYPE,
? ? ? ? ? ? ? ? ? ? ? ? ? C.SU_CODE,
? ? ? ? ? ? ? ? ? ? ? ? ? W.SU_NAME) FZ
? ? ? ON FZ.WAREHOUSE_CODE_OW = A.DATA_SOURCE
? ? AND FZ.FINISH_DATE = A.PICKING_DATE
? ? LEFT JOIN ( -- 計(jì)算高位揀選量,以計(jì)算分?jǐn)偙壤?/p>
? ? ? ? ? ? ? SELECT DATA_SOURCE,
? ? ? ? ? ? ? ? ? ? ? PICKING_DATE,
? ? ? ? ? ? ? ? ? ? ? SUBAREA_SIGN,
? ? ? ? ? ? ? ? ? ? ? PRODUCT_CLASS,
? ? ? ? ? ? ? ? ? ? ? SI_COUNT,
? ? ? ? ? ? ? ? ? ? ? SUM(SI_COUNT) OVER(PARTITION BY DATA_SOURCE, PICKING_DATE) AS TOTAL_SICOUNT,
? ? ? ? ? ? ? ? ? ? ? SI_COUNT / SUM(SI_COUNT) OVER(PARTITION BY DATA_SOURCE, PICKING_DATE) SPLIT_RATIO
? ? ? ? ? ? ? ? FROM (SELECT T.DATA_SOURCE,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? TRUNC(T.UPDATED, 'DD') PICKING_DATE,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? L.SUBAREA_SIGN,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? P.PRODUCT_CLASS,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? COUNT(T.ITEM_SERNO) SI_COUNT
? ? ? ? ? ? ? ? ? ? ? ? ? FROM dsafdfds T
? ? ? ? ? ? ? ? ? ? ? ? ? LEFT JOIN dfsds.fds WH
? ? ? ? ? ? ? ? ? ? ? ? ? ? ON WH.WAREHOUSE_CODE_OW = T.DATA_SOURCE
? ? ? ? ? ? ? ? ? ? ? ? ? LEFT JOIN sdfdf .fdsfds L
? ? ? ? ? ? ? ? ? ? ? ? ? ? ON L.WAREHOUSE_CODE = WH.WAREHOUSE_VALUE
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? --? AND L.SUBAREA_NAME=T.PICKING_SUBAREA_NAME
? ? ? ? ? ? ? ? ? ? ? ? ? AND L.LOCATOR_SERNO = T.HOLDING_LOCATOR_SERNO
? ? ? ? ? ? ? ? ? ? ? ? ? AND L.IS_ACTIVE = 'Y'
? ? ? ? ? ? ? ? ? ? ? ? ? AND L.IS_DELETE = 'N'
? ? ? ? ? ? ? ? ? ? ? ? ? LEFT JOIN fdsds.dfdsfds P
? ? ? ? ? ? ? ? ? ? ? ? ? ? ON P.PRODUCT_CODE = T.MERCHANDISE_SERNO
? ? ? ? ? ? ? ? ? ? ? ? WHERE L.SUBAREA_SIGN = '存儲(chǔ)庫位'
? ? ? ? ? ? ? ? ? ? ? ? ? AND T.ITEM_SERNO IS NOT NULL -- 異常揀選情況下單品條碼為空
? ? ? ? ? ? ? ? ? ? ? ? GROUP BY T.DATA_SOURCE,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? TRUNC(T.UPDATED, 'DD'),
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? L.SUBAREA_SIGN,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? P.PRODUCT_CLASS)) R
? ? ? ON R.DATA_SOURCE = A.DATA_SOURCE
? ? AND R.PICKING_DATE = A.PICKING_DATE
? ? AND R.PRODUCT_CLASS = A.PRODUCT_CLASS
? ORDER BY A.DATA_SOURCE, A.PICKING_DATE, A.PRODUCT_CLASS);
查詢實(shí)例:
SELECT 倉庫名稱,
?????? 工單號(hào),
?????? M碼,
?????? 商品規(guī)格,
?????? ABC分類,
?????? 移入庫位,
?????? 移入分區(qū),
?????? 移入?yún)^(qū)域,
?????? 可用庫存,
?????? 移庫時(shí)間,
?????? 移庫單品數(shù),
?????? 操作人
? FROM (select T.*, ROWNUMRN
?????????from WMS_TRANS_WORK_CHECK_USKY_V T
????????WHERE 移庫時(shí)間 >= TRUNC(SYSDATE - 1, 'DD')
??????????AND 移庫時(shí)間 < TRUNC(SYSDATE, 'DD')
??????????AND 倉庫名稱 IN ('USKY Warehouse', 'USKY2 Warehouse', 'USKY3 Warehouse')
??????????AND 移入?yún)^(qū)域='揀選庫位'
)
WHERE RN
<= 100;