Oracle 数据库 常见SQL语法

简介: Oracle 数据库 常见SQL语法

1:显示某一年某月的数据(包括不存在的数据)我如果想查都要查出来

SELECT 
    '2019' AS 年度,
    a.month AS 月份, 
    NVL(b.调试台设备利用率, 0) AS 调试台设备利用率,
    NVL(b.震动台设备设备利用率, 0) AS 震动台设备设备利用率,
    NVL(b.电装标贴设备利用率, 0) AS 电装标贴设备利用率,
    NVL(b.调试台故障率, 0) AS 调试台故障率,
    NVL(b.震动台故障率, 0) AS 震动台故障率,
    NVL(b.电装标贴故障率, 0) AS 电装标贴故障率
FROM (
    SELECT TO_CHAR(ADD_MONTHS(TO_DATE(CONCAT('2023','0101'), 'YYYYMMDD'), LEVEL - 1), 'fmMonth') AS month
    FROM DUAL
    CONNECT BY LEVEL <= 12
) a
LEFT JOIN (
    SELECT 
        SUBSTR(STAT_TIME,1,4) AS 年份,
        MONTH_DATA || '月' AS 月份,
        NVL(DEBUG_EQUIPMENT_TREND, 0) AS 调试台设备利用率 ,
        NVL(SHAKING_EQUIPMENT_TREND, 0) AS 震动台设备设备利用率 , 
        NVL(STICKERS_EQUIPMENT_TREND, 0) AS 电装标贴设备利用率,
        NVL(DEBUG_EQUIPMENT_FAILURERATE, 0) AS 调试台故障率,
        NVL(SHAKING_EQUIPMENT_FAILURERATE, 0) AS 震动台故障率,
        NVL(STICKER_EQUIPMENT_FAILURERATE, 0) AS 电装标贴故障率  
    FROM BRAIN.DM_EQUIPMENTUTILIZATION_TREND
    WHERE ORG_NAME = '17所' AND  SUBSTR(STAT_TIME,1,4) = '2019'
) b ON a.month = b.月份
ORDER BY a.month;
--拼接月份的另外一种写法  使用concat 函数,进行字符串拼接
concat(TO_CHAR(TO_DATE(B.DATEVALUE, 'YYYYMM'), 'MM'),'月')

结果:

4a4b44095d5e4c63937d7702a58a4faf.png

2:查询当前年以及往后俩年的数据

select to_char(years) 年份 from 
(select TO_CHAR(Sysdate, 'YYYY') - level + 1 years from dual connect by level <= 3)

结果:

a554a9ce284e4f8ea06aed922be52dc7.png

3:查询当前年前5年的年份

SELECT TO_CHAR(ADD_MONTHS
(to_date( ${年度选择} ,'yyyy'), (1 - ROWNUM)*12), 'YYYY') YEAR_LIST 
FROM DUAL CONNECT BY ROWNUM <= 5
// ${年度选择}     输入你想要的字符串年份,获取当年前五年

c6fad816b74544299452146d65c8f785.png

4:查询当前年的数据(不存在则显示 0),

SELECT A.month AS 每年月份 ,
nvl(A.目标值,0) AS 目标值, 
NVL(B.完成率,0) AS 完成率 
from(
SELECT
  TO_CHAR(ADD_MONTHS(TO_DATE(CONCAT( ${年度选择}  ,'0101'), 'YYYYMMDD'), LEVEL - 1), 'mm') || '月' AS month,
  ROUND(LEVEL / 12.0 * 100, 2) AS 目标值
FROM
  DUAL
CONNECT BY
  LEVEL <= 12) A
left join 
(SELECT 
NVL(REFORM_TASK_COM_RATE,0) AS 完成率,
SUBSTR(STAT_TIME,5,6)|| '月'  AS 每年月份
FROM  BRAIN.DM_ZHGL_REFORM_TASK_INFO 
WHERE  SUBSTR(STAT_TIME,1,4) =   ${年度选择}  ) 
B 
ON A.month = B.每年月份

结果:a84b02a0404d4c51bde8e1ef115a76d9.png

5:  3/14_设备情况_各类设备停机率年度趋势

SELECT 
 '' AS 唯一标识,
 a.ORGCODE AS 单位编号,
 a.ORGNAME AS 单位名称,
 a.ORGMANLEVEL AS 单位层级,
 NVL(a.统计日期类别,0) AS  统计日期类别,
 NVL(b.DATA_DATE,0) AS 统计日期,
 NVL(b.调试台故障率,0) AS 调试台故障率 ,
 NVL(b.电装标贴故障率,0) AS 电装标贴故障率,
 NVL(b.震动台故障率,0) AS  震动台故障率,
 NVL(b.CREATE_TIME,0) AS 创建时间,
 sysdate AS 更新时间
 FROM (
  SELECT ORGCODE , ORGNAME ,ORGMANLEVEL,
 '02' AS 统计日期类别
 FROM BRAIN.DIM_ORG WHERE ORGCODE = 'INST17'
 ) a 
 LEFT JOIN(
 SELECT  
 ORG_NO,
 ORG_NAME,
 DATA_DATE,
 NVL(a.调试台故障率,0) AS 调试台故障率,
 NVL(a.电装标贴故障率,0) AS 电装标贴故障率,
 NVL(a.震动台故障率,0) AS 震动台故障率,
 CREATE_TIME 
  FROM ( 
   SELECT ROW_NUMBER() OVER(PARTITION BY ORG_NO,TO_CHAR(DATA_DATE, 'YYYYMM') 
ORDER BY DATA_DATE DESC, CREATE_TIME DESC) rn,
          ORG_NO,
          ORG_NAME,
          TO_CHAR(DATA_DATE,'YYYYMM') AS DATA_DATE ,
          decode(DZBT_SBJHKDSJ,0,0,round((NVL(DZBT_GZTJSJ,0) / DZBT_SBJHKDSJ),2)) AS 调试台故障率,
          decode(TST_SBJHKDSJ,0,0,round((NVL(TST_GZTJSJ,0) / TST_SBJHKDSJ),2)) AS 电装标贴故障率,
          decode(ZDT_SBJHKDSJ,0,0,round((NVL(ZDT_GZTJSJ,0) / ZDT_SBJHKDSJ),2)) AS 震动台故障率,
          CREATE_TIME
          FROM BRAIN.DS_EQUIPMENT_GZLINFO
            ) a
 where a.rn = 1
 ) b
 ON a.ORGNAME = b.ORG_NAME

结果:

2f42a6ed96ec4399a521723be09375af.png

5:  3/14_设备情况_设备故障指标统计

SELECT 
'' AS 唯一标识,
a.ORGCODE AS 单位编号,
a.ORGNAME AS 单位名称,
a.ORGMANLEVEL AS 单位层级,
b.数据日期 AS 数据日期,
a.统计日期类别 AS 统计日期类别,
b.电装标贴总数 AS 电装标贴总数,
b.调试台总数 AS 调试台总数,
b.振动台总数 AS 振动台总数,
b.电装标贴设备发生故障设备数量 AS 电装标贴设备发生故障设备数量,
b.调试台设备发生故障设备数量 AS  调试台设备发生故障设备数量,
b.振动台设备发生故障设备数量 AS  振动台设备发生故障设备数量,
b.设备电装表贴发生故障总数 AS 设备电装表贴发生故障总数,
b.设备调试台发生故障总数 AS 设备调试台发生故障总数,
b.设备振动台发生故障总数 AS 设备振动台发生故障总数,
b.设备利用率 AS 设备利用率,
b.设备故障率 AS 设备故障率,
sysdate AS  更新时间, 
b.CREATE_TIME AS 创建时间
FROM (
SELECT 
ORGCODE,
ORGNAME,
ORGMANLEVEL,
'04' AS 统计日期类别
FROM BRAIN.DIM_ORG WHERE ORGCODE = 'INST17'
 ) a 
LEFT JOIN(
SELECT 
  a.机构编号 AS 机构编号,
  a.机构名称 AS 机构名称,
  a.电装标贴总数 AS 电装标贴总数,
  a.调试台总数 AS 调试台总数,
  a.振动台总数 AS 振动台总数,
  a.电装标贴设备发生故障设备数量 AS 电装标贴设备发生故障设备数量,
  a.调试台设备发生故障设备数量 AS 调试台设备发生故障设备数量,
  a.振动台设备发生故障设备数量 AS 振动台设备发生故障设备数量,
  a.设备电装表贴发生故障总数 AS 设备电装表贴发生故障总数 ,
  a.设备调试台发生故障总数 AS 设备调试台发生故障总数,
  a.设备振动台发生故障总数 AS 设备振动台发生故障总数,
  a.设备利用率 AS 设备利用率,
  a.设备故障率 AS 设备故障率,
  a.LATEST_VALUE AS 最新时间,                          
  a.数据日期 AS 数据日期,
  a.CREATE_TIME AS CREATE_TIME
  FROM ( 
   SELECT ROW_NUMBER() OVER(PARTITION BY ORG_NO,TO_CHAR(DATA_DATE, 'YYYY') 
ORDER BY DATA_DATE DESC, CREATE_TIME DESC) rn,
               ORG_NO 机构编号,
               NVL(DENSO_NUM,0) AS 电装标贴总数,
               NVL(COMMISSION_BENCH_NUM,0) AS 调试台总数,
               NVL(VIBRATION_TABLE_NUM,0) AS 振动台总数,
               NVL(DZBT_FS_GZ_EQUIPMENT_NUM,0) AS 电装标贴设备发生故障设备数量,
               NVL(TST_FS_GZ_EQUIPMENT_NUM,0) AS 调试台设备发生故障设备数量,
               NVL(ZDT_FS_GZ_EQUIPMENT_NUM,0) AS 振动台设备发生故障设备数量,
               NVL(DZBT_EQUIPMENT_GZ_TOTAL,0) AS 设备电装表贴发生故障总数,
               NVL(TST_EQUIPMENT_GZ_TOTAL,0) AS 设备调试台发生故障总数,
               NVL(ZDT_EQUIPMENT_GZ_TOTAL,0) AS 设备振动台发生故障总数,  
               NVL(EQUIPMENT_ALL_LYV,0) AS 设备利用率,
               NVL(EQUIPMENT_ALL_GZL,0) AS 设备故障率,
               NVL(DATA_DATE,0) AS LATEST_VALUE,
               ORG_NAME AS 机构名称,
               TO_CHAR(DATA_DATE, 'YYYY') AS 数据日期,
               NVL(CREATE_TIME,0) AS CREATE_TIME
          FROM BRAIN.DS_PRODUCT_EQUIP_INFO 
            ) a
 where a.rn = 1
 )b 
 ON a.ORGNAME = b.机构名称

6: 设备情况故障分析

SELECT 
     ${年度}  AS 年度,
    a.month AS 月份, 
    NVL(b.调试台设备利用率, 0) AS 调试台设备利用率,
    NVL(b.震动台设备设备利用率, 0) AS 震动台设备设备利用率,
    NVL(b.电装标贴设备利用率, 0) AS 电装标贴设备利用率,
    NVL(b.调试台故障率, 0) AS 调试台故障率,
    NVL(b.震动台故障率, 0) AS 震动台故障率,
    NVL(b.电装标贴故障率, 0) AS 电装标贴故障率
FROM (
    SELECT TO_CHAR(ADD_MONTHS(TO_DATE(CONCAT('2022','0101'), 'YYYYMMDD'), LEVEL - 1), 'fmMonth') AS month                         //去掉0
    FROM DUAL
    CONNECT BY LEVEL <= 12
) a
LEFT JOIN (
    SELECT 
        SUBSTR(STAT_TIME,1,4) AS 年份,
        MONTH_DATA || '月' AS 月份,
        NVL(DEBUG_EQUIPMENT_TREND, 0) AS 调试台设备利用率 ,
        NVL(SHAKING_EQUIPMENT_TREND, 0) AS 震动台设备设备利用率 , 
        NVL(STICKERS_EQUIPMENT_TREND, 0) AS 电装标贴设备利用率,
        NVL(DEBUG_EQUIPMENT_FAILURERATE, 0) AS 调试台故障率,
        NVL(SHAKING_EQUIPMENT_FAILURERATE, 0) AS 震动台故障率,
        NVL(STICKER_EQUIPMENT_FAILURERATE, 0) AS 电装标贴故障率  
    FROM BRAIN.DM_EQUIPMENTUTILIZATION_TREND
    WHERE ORG_NAME = '17所' AND SUBSTR(STAT_TIME,1,4) =  ${年度} 
) b ON a.month = b.月份
ORDER BY a.month

7: 查询1年个的12个月下的数据

SELECT TO_CHAR(ADD_MONTHS(TO_DATE(CONCAT('2022','0101'), 'YYYYMMDD'), LEVEL - 1), 'fmMonth') AS month                //去掉 0
    FROM DUAL
    CONNECT BY LEVEL <= 12

结果:

065661f7d77d4ec08894d9f29850797d.png

8:  3种不同种类的设备数量,按照每条取最新值, 一年一共有3条不同种类设备的最新数据

WITH years AS (
  select to_char(years) year from 
(select TO_CHAR(Sysdate, 'YYYY') - level + 1 years from dual connect by level <= 3)
), types AS (
  SELECT '调试台设备数量' AS 设备种类, '01' AS EQUIPMENT_TYPE FROM dual
  UNION ALL
  SELECT '电装标贴设备数量' AS 设备种类, '02' AS EQUIPMENT_TYPE FROM dual
  UNION ALL
  SELECT '震动台设备数量' AS 设备种类, '03' AS EQUIPMENT_TYPE FROM dual
)
SELECT 
  a.year AS 年份,
  t.设备种类,
  NVL(b.设备总数, 0) AS 设备总数,
  NVL(b.发生故障的设备数量, 0) AS 发生故障的设备数量,
  NVL(b.发生故障总数, 0) AS 发生故障总数,
  NVL(b.设备完好率, 0) AS 设备完好率,
  NVL(b.设备停机率, 0) AS 设备停机率
FROM years a
CROSS JOIN types t
LEFT JOIN (
  SELECT 
    EQUIPMENT_TYPE,
    STAT_TIME  AS 数据日期,
    EQUIPMENT_FAILURE_TOTAL AS 设备总数,
    EQUIPMENT_FAILURE_NUM AS 发生故障的设备数量,
    EQUIPMENT_FAILURE_TOTAL AS 发生故障总数,
    EQUIPMENT_UPTIME AS 设备完好率,
    EQUIPMENT_FAILURE_DOWNTIMERATE AS 设备停机率
  FROM BRAIN.DM_EQUIPMENT_FAILURE_METRIC
  WHERE EQUIPMENT_TYPE IN ('01', '02', '03')
) b ON 
t.EQUIPMENT_TYPE = b.EQUIPMENT_TYPE 
AND a.year = b.数据日期 
AND t.设备种类 = 
CASE t.EQUIPMENT_TYPE 
WHEN '01' 
THEN '调试台设备数量' 
WHEN '02' THEN '电装标贴设备数量' 
WHEN '03' THEN '震动台设备数量' 
END
WHERE a.year =  ${年度}

9:设备故障率

SELECT
A.month AS 月份,
NVL(B.调试台故障率,0) AS 调试台故障率,
NVL(B.震动台故障率,0) AS 震动台故障率,
NVL(B.电装标贴故障率,0) AS 电装标贴故障率,
 ${年度}  AS 统计日期
from(
SELECT
  TO_CHAR(ADD_MONTHS(TO_DATE(CONCAT(   ${年度}    ,'0101'), 'YYYYMMDD'), LEVEL - 1), 'mm') || '月' AS month
FROM DUAL CONNECT BY LEVEL <= 12
) A
left join 
(
SELECT 
  NVL(DEBUG_EQUIPMENT_FAILURERATE,0) AS 调试台故障率,
  NVL(SHAKING_EQUIPMENT_FAILURERATE,0) AS 震动台故障率,
  NVL(STICKER_EQUIPMENT_FAILURERATE,0) AS 电装标贴故障率,
  SUBSTR(STAT_TIME,1,4) AS 统计日期,
  SUBSTR (STAT_TIME,5,6)|| '月'  AS 每年月份
  FROM BRAIN.DM_EQUIPMENT_GZTJV_TREND
 WHERE  SUBSTR(STAT_TIME,1,4) =     ${年度} 
 ) 
B 
ON A.month = B.每年月份
ORDER BY 月份 ASC

运行结果:

1456bb04eaee426098f5efdc90df7993.png

10:设备利用率

SELECT
 ${年度}  AS 统计日期,
A.month AS 月份,
NVL(B.调试台利用率,0) AS 调试台利用率,
NVL(B.震动台利用率,0) AS 震动台利用率,
NVL(B.电装标贴利用率,0) AS 电装标贴利用率
from(
SELECT
  TO_CHAR(ADD_MONTHS(TO_DATE(CONCAT( ${年度}    ,'0101'), 'YYYYMMDD'), LEVEL - 1), 'mm') || '月' AS month
FROM DUAL CONNECT BY LEVEL <= 12) A
left join 
(SELECT 
  NVL(COMMISSION_BENCH_OCCUPANCY,0) AS 调试台利用率,
  NVL(VIBRATION_TABLE_OCCUPANCY,0) AS 震动台利用率,
  NVL(DENSO_OCCUPANCY,0) AS 电装标贴利用率,
  SUBSTR(STAT_TIME,1,4) AS 统计日期,
  SUBSTR (STAT_TIME,5,6)|| '月'  AS 每年月份
  FROM BRAIN.DM_EQUIPMENT_OCCUPANCY
WHERE  SUBSTR(STAT_TIME,1,4) =     ${年度}    ) 
B 
ON A.month = B.每年月份
ORDER BY 月份 ASC

结果:

656fb820652a47f792a768ccd9561b3c.png

11:设备故障指标统计

SELECT 
A.年份 AS 年份,
NVL(B.电装表贴设备总数,0) AS  电装表贴设备总数,
NVL(B.调试台设备总数,0) AS 调试台设备总数,
NVL(B.振动台设备总数,0) AS 振动台设备总数,
NVL(B.电装表贴设备发生故障设备数量,0) AS 电装表贴设备发生故障设备数量,
NVL(B.调试台设备发生故障的设备数量,0) AS 调试台设备发生故障的设备数量,
NVL(B.振动台设备发生故障的设备数量,0) AS 振动台设备发生故障的设备数量,
NVL(B.电装表贴发生故障总数,0) AS 电装表贴发生故障总数,
NVL(B.调试台发生故障总数,0) AS 调试台发生故障总数,
NVL(B.振动台发生故障总数,0) AS 振动台发生故障总数,
NVL(B.设备完好率,0) AS 设备完好率,         --round(NVL(B.设备完好率,0),4)  AS 设备完好率
NVL(B.设备停机率,0) AS  设备停机率         --round(NVL(B.设备停机率,0),4)  AS 设备停机率
FROM (
select to_char(years) 年份 from (
select TO_CHAR(Sysdate, 'YYYY') - level + 1 years 
from dual connect by level <= 3)
) A 
LEFT JOIN (
SELECT 
NVL(DZBT_TOTAL_NUM,0) AS 电装表贴设备总数,
NVL(TST_TOTAL_NUM,0)  AS 调试台设备总数,
NVL(ZDT_TOTAL_NUM,0)  AS 振动台设备总数,
NVL(DZBT_FAILURE_NUM,0) AS 电装表贴设备发生故障设备数量,
NVL(TST_FAILURE_NUM,0)  AS 调试台设备发生故障的设备数量,
NVL(ZDT_FAILURE_NUM,0)  AS 振动台设备发生故障的设备数量,
NVL(DZBT_FAILURE_TOTAL,0) AS 电装表贴发生故障总数,
NVL(TST_FAILURE_TOTAL,0)  AS 调试台发生故障总数,
NVL(ZDT_FAILURE_TOTAL,0)  AS 振动台发生故障总数,
NVL(EQUIPMENT_UPTIME,0)   AS 设备完好率,
NVL(EQUIPMENT_FAILURE_DOWNTIMERATE,0) AS 设备停机率,
NVL(STAT_TIME,0)  AS 统计日期
FROM BRAIN.DM_EQUIPMENT_FAILURE_METRIC
) B
ON A.年份  = B.统计日期
WHERE A.年份= ${年度}

运行结果:

145aa6c6dec149cfb0f0aad5991f711d.png

12:工人设备汇总--解决了表管理数据重复问题

SELECT 
'' AS 唯一标识,
a.单位编号 ,
'0'  AS 是否汇总,
'04' AS 数据密度, 
NVL(a.工人总数,0) AS 工人总数,
NVL(b.电装表贴数量,0) AS 电装表贴数量,
NVL(b.调试台数量,0) AS 调试台数量,
NVL(b.振动台数量,0) AS 振动台数量,
NVL(a.数据日期,SYSDATE) AS 数据日期,
NVL(a.统计日期,SYSDATE) AS 统计日期
FROM (
--DS_WOKER_WORK_HOUR_INFO 工人工时信息表 的每年最新数据
--DS_WOKER_WORK_HOUR_INFO 工人工时信息表 的每年最新数据
SELECT 
a.ORG_NO AS 单位编号,
NVL(a.WOKER_TOTAL_NUM,0) AS 工人总数 ,
NVL(a.DATA_DATE,SYSDATE) AS 数据日期,
NVL(a.CREATE_TIME,SYSDATE) AS 统计日期
  FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ORG_NO,TO_CHAR(DATA_DATE, 'YYYY') 
ORDER BY DATA_DATE DESC, CREATE_TIME DESC) rn,
               ORG_NO,
               NVL(WOKER_TOTAL_NUM,0) AS WOKER_TOTAL_NUM,
               DATA_DATE,
               CREATE_TIME
          FROM BRAIN.DS_WOKER_WORK_HOUR_INFO
) a
 where a.rn = 1) a
lEFT JOIN 
 (--DS_PRODUCT_EQUIP_INFO-生产设备信息 每年最新数据
SELECT 
a.ORG_NO AS 单位编号,
NVL(a.DENSO_NUM,0) AS 电装表贴数量,
NVL(a.COMMISSION_BENCH_NUM,0) AS 调试台数量,
NVL(a.VIBRATION_TABLE_NUM,0) AS 振动台数量,
NVL(a.DATA_DATE,SYSDATE) AS DATA_DATE
  FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ORG_NO,TO_CHAR(DATA_DATE, 'YYYY') 
ORDER BY DATA_DATE DESC, CREATE_TIME DESC) rn,
               ORG_NO,
               NVL(DENSO_NUM,0) AS DENSO_NUM,  
               NVL(COMMISSION_BENCH_NUM,0) AS COMMISSION_BENCH_NUM,
               NVL(VIBRATION_TABLE_NUM,0) AS VIBRATION_TABLE_NUM,
               DATA_DATE
          FROM BRAIN.DS_PRODUCT_EQUIP_INFO
) a
 where a.rn = 1
) b
 ON a.单位编号 = b.单位编号  AND TO_CHAR(a.数据日期,'YYYY') = TO_CHAR(b.DATA_DATE,'YYYY')

运行结果:

071051c19e0d42db8a950cd2db7c7272.png

13:多种计算求结果

SELECT 
'04' AS 数据粒度,
NVL(最长工序耗时,0) AS 最长工序耗时,
NVL(平均工序耗时,0) AS 平均工序耗时,
DECODE(最长工序耗时, 0, 0, 
        CASE WHEN 最长工序耗时 = 平均工序耗时 THEN 100
             ELSE ROUND( NVL(平均工序耗时, 0) / 最长工序耗时 , 2) * 100
        END
) AS 线平衡率 ,
DECODE(COALESCE(最长工序耗时, 0), 0, 0, ROUND(20.83 * 24 * 60 / COALESCE(最长工序耗时, 0), 2)) AS 月度产能,
(1- DECODE(最长工序耗时, 0, 0, 
        CASE WHEN 最长工序耗时 = 平均工序耗时 THEN 1
             ELSE ROUND(NVL(平均工序耗时, 0) / 最长工序耗时 , 2)
        END
) ) * 100 AS  平衡损失率,
NVL(模件电裝单人工作耗时,0) AS 模件电裝单人工作耗时,
NVL(整机电裝单人工作耗时,0) AS 整机电裝单人工作耗时,
NVL(单机调试单人工作耗时,0) AS 单机调试单人工作耗时,
NVL(单板调试单人工作耗时,0) AS 单板调试单人工作耗时,
NVL(测发控系统联调单人工作耗时,0) AS 测发控系统联调单人工作耗时,
NVL(控制系统联调单人工作耗时,0) AS 控制系统联调单人工作耗时,
NVL(数据日期,SYSDATE) AS 数据日期,
NVL(统计日期,SYSDATE) AS 统计日期
FROM (
SELECT 
 GREATEST(
  NVL(a.模件电裝单人工作耗时,0),
  NVL(a.整机电裝单人工作耗时,0),
  NVL(a.单机调试单人工作耗时,0),
  NVL(a.单板调试单人工作耗时,0),
  NVL(a.测发控系统联调单人工作耗时,0),
  NVL(a.控制系统联调单人工作耗时,0)
  ) AS  最长工序耗时,
 ROUND((NVL(a.模件电裝单人工作耗时,0) + NVL(a.整机电裝单人工作耗时,0) + NVL(a.单机调试单人工作耗时,0) + NVL(a.单板调试单人工作耗时,0) + NVL(a.测发控系统联调单人工作耗时,0) + NVL(a.控制系统联调单人工作耗时,0))/6,2) AS 平均工序耗时,
 NVL(a.模件电裝单人工作耗时,0) AS 模件电裝单人工作耗时,
 NVL(a.整机电裝单人工作耗时,0) AS 整机电裝单人工作耗时,
 NVL(a.单机调试单人工作耗时,0) AS 单机调试单人工作耗时,
 NVL(a.单板调试单人工作耗时,0) AS 单板调试单人工作耗时,
 NVL(a.测发控系统联调单人工作耗时,0) AS 测发控系统联调单人工作耗时,
 NVL(a.控制系统联调单人工作耗时,0) AS 控制系统联调单人工作耗时,
 NVL(a.数据日期,SYSDATE)   AS 数据日期,
 NVL(a.CREATE_TIME,SYSDATE)   AS 统计日期
  FROM (
  SELECT ROW_NUMBER() OVER(PARTITION BY ORG_NO,TO_CHAR(DATA_DATE, 'YYYY') 
ORDER BY DATA_DATE DESC, CREATE_TIME DESC) rn,
 DECODE(MODEL_ELEC_INSTAL_NUM ,0,0,
            CASE WHEN MODEL_ELEC_INSTAL_TIME = MODEL_ELEC_INSTAL_NUM THEN 100
            ELSE ROUND(NVL(MODEL_ELEC_INSTAL_TIME,0) /  MODEL_ELEC_INSTAL_NUM ,2 )* 100 END ) AS 模件电裝单人工作耗时,
 DECODE(ELEC_INSTAL_NUM ,0,0,
                 CASE WHEN ELEC_INSTAL_TIME = ELEC_INSTAL_NUM THEN 100
            ELSE ROUND(NVL(ELEC_INSTAL_TIME,0) /  ELEC_INSTAL_NUM,2 ) * 100 END ) AS 整机电裝单人工作耗时,
 DECODE(SINGLE_TEST_NUM ,0,0,
                 CASE WHEN SINGLE_TEST_TIME = SINGLE_TEST_NUM THEN 100
            ELSE ROUND(NVL(SINGLE_TEST_TIME,0) / SINGLE_TEST_NUM ,2 ) * 100 END ) AS  单机调试单人工作耗时,
 DECODE(BOARD_TEST_NUM ,0,0,
                 CASE WHEN BOARD_TEST_TIME = BOARD_TEST_NUM THEN 100
            ELSE ROUND(NVL(BOARD_TEST_TIME,0) / BOARD_TEST_NUM,2 ) * 100   END ) AS 单板调试单人工作耗时,
 DECODE(TEST_CONTROL_SYS_NUM ,0,0,
                 CASE WHEN TEST_CONTROL_SYS_TIME = TEST_CONTROL_SYS_NUM THEN 100
            ELSE ROUND(NVL(TEST_CONTROL_SYS_TIME,0) / TEST_CONTROL_SYS_NUM ,2) * 100 END) AS 测发控系统联调单人工作耗时,
 DECODE(CONTROL_SYS_NUM ,0,0,
                 CASE WHEN CONTROL_SYS_TIME = CONTROL_SYS_NUM THEN 100
            ELSE ROUND(NVL(CONTROL_SYS_TIME,0) / CONTROL_SYS_NUM,2) * 100 END) AS 控制系统联调单人工作耗时,
          DATA_DATE AS 数据日期,
          CREATE_TIME 
          FROM BRAIN.DS_PROD_PROC_RECORD
         ) a
 where a.rn = 1
)

结果:

9579372c426d4ae2928e68e3e3807dbc.png


目录
相关文章
|
7天前
|
SQL Oracle 关系型数据库
【Oracle】玩转Oracle数据库:装上去,飞起来!
本文是一篇关于Oracle数据库安装和使用的博文摘要。作者以轻松幽默的笔调介绍了自己在实验中掌握的Oracle数据库基本操作,包括使用组件查看命令、配置数据库监听器等。作者也分享了在实验中遇到的一些有趣问题,如SQL语句缺少分号导致的意外错误。此外,作者还强调了登录sys用户和启动实例加载数据库的注意事项,并鼓励读者面对挑战时保持乐观,不断提升自己的能力。整体风格风趣严谨,引人入胜。
63 0
|
24天前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之数据查询
【数据库SQL server】关系数据库标准语言SQL之数据查询
44 0
|
7天前
|
SQL 存储 关系型数据库
sql数据库的相关概念与底层介绍
sql数据库的相关概念与底层介绍
30 0
|
2天前
|
开发框架 Oracle 关系型数据库
ASP.NET实验室LIS系统源码 Oracle数据库
LIS是HIS的一个组成部分,通过与HIS的无缝连接可以共享HIS中的信息资源,使检验科能与门诊部、住院部、财务科和临床科室等全院各部门之间协同工作。 
16 4
|
3天前
|
Oracle 关系型数据库 数据处理
某教程学习笔记(一):10、oracle数据库注入
某教程学习笔记(一):10、oracle数据库注入
4 0
|
20天前
|
SQL
Sql语法:字段不为空
Sql语法:字段不为空
|
23天前
|
SQL 人工智能 运维
数据库基础入门 — SQL排序与分页
数据库基础入门 — SQL排序与分页
16 0
|
23天前
|
SQL 人工智能 运维
数据库基础入门 — SQL运算符
数据库基础入门 — SQL运算符
15 0
|
23天前
|
SQL 人工智能 运维
数据库基础入门 — SQL
数据库基础入门 — SQL
26 0
|
24天前
|
SQL 存储 数据库
【数据库SQL server】自学终极笔记
【数据库SQL server】自学终极笔记
73 0

热门文章

最新文章

推荐镜像

更多