（针对 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/cn.aspx?n=bbs/Database/M.1595486385.A.D77.html