（針對 SQL 語言的問題，用這個標題。請用 Ctrl+Y 砍掉這行） 資料庫名稱：oracle sql 資料庫版本： 內容/問題描述： 主要問題有二 一 欄位一 時間區間以 當日每小時 做間隔,不知道有沒有時間函數可以處理? 目前想到 以 CASE 作條件輸出 ； WITH 做虛擬表格 查詢 ； 製作 VIEW 觀視表 (和WITH 一樣) 二 達成率希望做個別統計 EX: 07:00 產量/100 ； 08:00 產量/50 主要卡在問題一的時間區隔處理, 沒有其他資料表有相關欄位可以做 JOINT 或 子查詢 請大家幫幫忙 資料表 G_SN_TRAVEL 欄位 OUT_PROCESS_TIME (DATE 機台過站時間戳記) 希望輸出查詢統計表如下 欄位一 欄位二 欄位三 CLOCK_TIME QTY(產量) RATE(達成率) 07:00 XXX XX % 08:00 XXX XX % 09:00 XXX XX % 已有 方案 都可以完成 問題一 方案一 SELECT (case to_char(A.OUT_PROCESS_TIME,'HH24') when '07' then '07:00~07:59' when '08' then '08:00~08:59' when '09' then '09:00~09:59' when '10' then '10:00~10:59' when '11' then '11:00~11:59' when '12' then '12:00~12:59' when '13' then '13:00~13:59' when '14' then '14:00~14:59' when '15' then '15:00~15:59' when '16' then '16:00~16:59' when '17' then '17:00~17:59' when '18' then '18:00~18:59' when '19' then '19:00~19:59' when '20' then '20:00~20:59' end) AS TIME_CLOCK, COUNT (A.OUT_PROCESS_TIME) AS QTY, TO_CHAR ((COUNT (A.OUT_PROCESS_TIME) /120),'0.000') AS RATE FROM SAJET.G_SN_TRAVEL A WHERE A.PROCESS_ID = '100032' AND to_char(A.OUT_PROCESS_TIME,'YYYYMMDD') = TO_CHAR(SYSDATE,'YYYYMMDD') GROUP BY (case to_char(A.OUT_PROCESS_TIME,'HH24') when '07' then '07:00~07:59' when '08' then '08:00~08:59' when '09' then '09:00~09:59' when '10' then '10:00~10:59' when '11' then '11:00~11:59' when '12' then '12:00~12:59' when '13' then '13:00~13:59' when '14' then '14:00~14:59' when '15' then '15:00~15:59' when '16' then '16:00~16:59' when '17' then '17:00~17:59' when '18' then '18:00~18:59' when '19' then '19:00~19:59' when '20' then '20:00~20:59' end) ORDER BY (case to_char(A.OUT_PROCESS_TIME,'HH24') when '07' then '07:00~07:59' when '08' then '08:00~08:59' when '09' then '09:00~09:59' when '10' then '10:00~10:59' when '11' then '11:00~11:59' when '12' then '12:00~12:59' when '13' then '13:00~13:59' when '14' then '14:00~14:59' when '15' then '15:00~15:59' when '16' then '16:00~16:59' when '17' then '17:00~17:59' when '18' then '18:00~18:59' when '19' then '19:00~19:59' when '20' then '20:00~20:59' end) ASC =============================== 方案二 with v_today(vday) as ( SELECT to_char(sysdate,'YYYYMMDD')FROM dual ), --select * from v_today v_G_SN_TRAVEL(v_WORK_ORDER,v_SERIAL_NUMBER,v_OUT_PROCESS_TIME,v_vc_time) as ( select a.WORK_ORDER ,a.SERIAL_NUMBER ,a.OUT_PROCESS_TIME ,to_char(a.OUT_PROCESS_TIME,'YYYYMMDDHH24MISS') as vc_time from SAJET.G_SN_TRAVEL a ,v_today b where 0=0 --and a.WORK_ORDER = 'MO20050015' and a.PROCESS_ID = '100032' and to_char(a.OUT_PROCESS_TIME,'YYYYMMDD') BETWEEN b.vday and b.vday ), --select * from v_G_SN_TRAVEL v_clock_07 (v_count_time) as ( select COUNT(v_vc_time) from v_G_SN_TRAVEL where to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '070000' and '079999' ), v_clock_08 (v_count_time) as ( select COUNT(v_vc_time) from v_G_SN_TRAVEL where to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '080000' and '089999' ), v_clock_09 (v_count_time) as ( select COUNT(v_vc_time) from v_G_SN_TRAVEL where to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '090000' and '099999' ), v_clock_10 (v_count_time) as ( select COUNT(v_vc_time) from v_G_SN_TRAVEL where to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '100000' and '109999' ), v_clock_11 (v_count_time) as ( select COUNT(v_vc_time) from v_G_SN_TRAVEL where to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '110000' and '119999' ), v_clock_12 (v_count_time) as ( select COUNT(v_vc_time) from v_G_SN_TRAVEL where to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '120000' and '129999' ), v_clock_13 (v_count_time) as ( select COUNT(v_vc_time) from v_G_SN_TRAVEL where to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '130000' and '139999' ), v_clock_14 (v_count_time) as ( select COUNT(v_vc_time) from v_G_SN_TRAVEL where to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '140000' and '149999' ), v_clock_15 (v_count_time) as ( select COUNT(v_vc_time) from v_G_SN_TRAVEL where to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '150000' and '159999' ), v_clock_16 (v_count_time) as ( select COUNT(v_vc_time) from v_G_SN_TRAVEL where to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '160000' and '169999' ), v_clock_17 (v_count_time) as ( select COUNT(v_vc_time) from v_G_SN_TRAVEL where to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '170000' and '179999' ), v_clock_18 (v_count_time) as ( select COUNT(v_vc_time) from v_G_SN_TRAVEL where to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '180000' and '189999' ), v_clock_19 (v_count_time) as ( select COUNT(v_vc_time) from v_G_SN_TRAVEL where to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '190000' and '199999' ), v_clock_20 (v_count_time) as ( select COUNT(v_vc_time) from v_G_SN_TRAVEL where to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '200000' and '209999' ), v_clock_21 (v_count_time) as ( select COUNT(v_vc_time) from v_G_SN_TRAVEL where to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '210000' and '219999' ), sum_clock_data (v_itm,v_scd) as ( select '07:00 ~ 07:59' as v_itm ,v_count_time from v_clock_07 union all select '08:00 ~ 08:59' as v_itm ,v_count_time from v_clock_08 union all select '09:00 ~ 09:59' as v_itm ,v_count_time from v_clock_09 union all select '10:00 ~ 10:59' as v_itm ,v_count_time from v_clock_10 union all select '11:00 ~ 11:59' as v_itm ,v_count_time from v_clock_11 union all select '12:00 ~ 12:59' as v_itm ,v_count_time from v_clock_12 union all select '13:00 ~ 13:59' as v_itm ,v_count_time from v_clock_13 union all select '14:00 ~ 14:59' as v_itm ,v_count_time from v_clock_14 union all select '15:00 ~ 15:59' as v_itm ,v_count_time from v_clock_15 union all select '16:00 ~ 16:59' as v_itm ,v_count_time from v_clock_16 union all select '17:00 ~ 17:59' as v_itm ,v_count_time from v_clock_17 union all select '18:00 ~ 18:59' as v_itm ,v_count_time from v_clock_18 union all select '19:00 ~ 19:59' as v_itm ,v_count_time from v_clock_19 union all select '20:00 ~ 20:59' as v_itm ,v_count_time from v_clock_20 union all select '21:00 ~ 21:59' as v_itm ,v_count_time from v_clock_21 ) select (v_itm)"Time Clock",(v_scd)"Output Qty" from sum_clock_data -- 從表象看起來我是個沒啥事的閒人╭(─╴─)╮ 其實我的真實身份是............研究僧 研究如何將自己脫離去死團方法.........................Orz --

※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 61.216.86.175 (臺灣) ※ 文章網址: https://webptt.com/m.aspx?n=bbs/Database/M.1595486385.A.D77.html