SQL Select Study

最新推荐文章于 2024-08-15 20:26:20 发布

WangBoSoft

最新推荐文章于 2024-08-15 20:26:20 发布

阅读量181

点赞数

文章标签： sql

本文链接：https://blog.csdn.net/Ritottt/article/details/105854542

版权

SQL Select Study Base OracleDB

SELECT LENGTH ('0123456789') STR_LENG,
       TO_CHAR (SYSDATE, 'YYYYMMDD') STR_DATE,
       TO_DATE ('20200501', 'YYYY-MM-DD') DATE_STR,
       SUBSTR ('ChinaShangHai', 6, 8) SUBSTR_STR,
       RPAD ('ABCD', 8, '*') RIGHT_FILL,
       LPAD ('ABCD', 8, '#') LEFT_FILL,
       TRIM ('   12345678   ') TRIM_SPACE,
       UPPER ('aABbcCdD') UPPER_STR,
       LOWER ('aABbcCdD') LOWER_STR
  FROM DUAL

在这里插入图片描述

SELECT WORK_DATE,WORK_SECTION,MO_NUMBER,MODEL_NAME,LINE_NAME,SECTION_NAME,GROUP_NAME,PASS_QTY,FAIL_QTY 
FROM SFISM4.R_STATION_REC_T WHERE WORK_DATE='20200429' AND LINE_NAME<>'REPAIR' 
AND GROUP_NAME IN ('FT','PT') AND WORK_SECTION=TO_CHAR(SYSDATE,'HH24') 
ORDER BY WORK_SECTION,MODEL_NAME;

在这里插入图片描述

SELECT MO_NUMBER, ITEM_1 AS BEGIN_RANGE, ITEM_2 AS END_RANGE,
       VER_1  AS VERSION_CODE, ITEM_3 AS PRINT_TIME,VER_4 AS MODEL_NAME
  FROM SFISM4.R_MO_EXT_T
 WHERE     ITEM_3 LIKE '20200429%'
       AND '358244086426154' BETWEEN ITEM_1 AND ITEM_2
       AND LENGTH (ITEM_1) = LENGTH ('358244086426154');

在这里插入图片描述

SELECT SERIAL_NUMBER,MO_NUMBER,MODEL_NAME,GROUP_NAME,IN_STATION_TIME,ATE_STATION_NO
  FROM SFISM4.R_SN_DETAIL_T
 WHERE   TO_CHAR(IN_STATION_TIME,'YYYYMMDD') BETWEEN '20200420' AND '20200429' 
  ORDER BY SERIAL_NUMBER,IN_STATION_TIME;

  SELECT SERIAL_NUMBER,MO_NUMBER,MODEL_NAME,GROUP_NAME,IN_STATION_TIME,ATE_STATION_NO
  FROM SFISM4.R_SN_DETAIL_T
 WHERE   IN_STATION_TIME  BETWEEN TO_DATE('20200420','YYYYMMDD') AND TO_DATE('20200429','YYYYMMDD')
 ORDER BY SERIAL_NUMBER,IN_STATION_TIME;

在这里插入图片描述

SELECT LENGTH ('0123456789') STR_LENG,                  --Length:计算字符窜长度
       TO_CHAR (SYSDATE, 'YYYYMMDD') STR_DATE,      --To_Char:把日期格式转换为指定的字符窜格式
       TO_DATE ('20200501', 'YYYY-MM-DD') DATE_STR,  --To_Date:把字符窜转换为日期格式
       SUBSTR ('ChinaShangHai', 6, 8) SUBSTR_STR,       --Substr:从字符窜中指定位置,截取指定长度字符
       RPAD ('ABCD', 8, '*') RIGHT_FILL,                          --Rpad字符窜右侧不足指定长度补指定字符
       LPAD ('ABCD', 8, '#') LEFT_FILL,                            --Lpad字符窜左侧不足指定长度补指定字符
       TRIM ('   12345678   ') TRIM_SPACE,                     --Trim去掉前后字符窜中的空格
       UPPER ('aABbcCdD') UPPER_STR,                           --Upper把字符窜转换为大写
       LOWER ('aABbcCdD') LOWER_STR,                        --Lower把字符窜转换为小写
       MOD (14, 3) ARITHMETICAL,                                 --Mod求两个数字的余数
       ROUND ('12.985', 2) ROUND1,                                --Round保留两位小数,会四舍五入
       ROUND ('12.985') ROUND2,                                    --Round保留整数,会四舍五入
       REPLACE ('This is Passed', 'is', 'was') REPLACE_STR,--Replace替换指定字符
       INSTR ('[<U816EB200429012345', 'EB') INSTR_LOC,--Instr指定字符出现的位置
       SUBSTR ('[<U816EB200429012345',
               INSTR ('[<U816EB200429012345', 'EB'),
               14)  SUBSTR_INSTR,                                   --Substr和Instr的组合
       DECODE (TO_CHAR (SYSDATE, 'MM'), '10', 'A','11', 'B','12', 'C',
               SUBSTR (TO_CHAR (SYSDATE, 'MM'), 2)) DECODE_CHAR, --Decode相当于If条件语句
        TO_CHAR (SYSDATE, 'WW') ||NVL(NULL,'ABCDEF') NVL_STR    --NVL,第一个参数为空,则返回第二个
  FROM DUAL;

在这里插入图片描述

SELECT ROWNUM,MO_NUMBER,MODEL_NAME, GROUP_NAME,YIELD_RATE FROM
(SELECT MO_NUMBER,MODEL_NAME, GROUP_NAME,
ROUND(SUM(PASS_QTY)/SUM(TOTAL_QTY),4)*100||'%' AS YIELD_RATE FROM
(SELECT WORK_DATE, MO_NUMBER,MODEL_NAME, LINE_NAME,GROUP_NAME,
            PASS_QTY, FAIL_QTY,PASS_QTY+ FAIL_QTY AS TOTAL_QTY
  FROM SFISM4.R_STATION_REC_T
 WHERE     WORK_DATE = '20200429' AND LINE_NAME <> 'REPAIR'
       AND GROUP_NAME IN ('FT', 'PT') AND PASS_QTY+ FAIL_QTY>0)
       GROUP BY MO_NUMBER,MODEL_NAME, GROUP_NAME
       HAVING SUM(PASS_QTY)>0 );

在这里插入图片描述

SELECT TO_CHAR(SYSDATE,'YYYYWW') YEAR_WEEK FROM DUAL
UNION
SELECT TO_CHAR(SYSDATE,'YYYYWW') YEAR_WEEK FROM DUAL;

SELECT TO_CHAR(SYSDATE,'YYYYWW') YEAR_WEEK FROM DUAL
UNION ALL
SELECT TO_CHAR(SYSDATE,'YYYYWW') YEAR_WEEK FROM DUAL;

SELECT TO_CHAR(SYSDATE,'YYYYWW') YEAR_WEEK FROM DUAL
MINUS
SELECT TO_CHAR(SYSDATE,'YYYYIW') YEAR_WEEK FROM DUAL;

SELECT DISTINCT YEAR_WEEK  FROM(
SELECT TO_CHAR(SYSDATE,'YYYYWW') YEAR_WEEK FROM DUAL
UNION ALL
SELECT TO_CHAR(SYSDATE,'YYYYWW') YEAR_WEEK FROM DUAL );

在这里插入图片描述

SELECT MO_NUMBER,MO_TYPE,MODEL_NAME,VERSION_CODE,TARGET_QTY,
            OUTPUT_QTY,ROUND(SYSDATE-MO_START_DATE)*24 MO_HOURS
FROM SFISM4.R_MO_BASE_T WHERE  MO_NUMBER IN (
SELECT DISTINCT MO_NUMBER FROM SFISM4.R_STATION_REC_T
WHERE  WORK_DATE = '20200429' ) 
AND CLOSE_FLAG='2' AND MO_CREATE_DATE>SYSDATE-5 
AND MO_NUMBER LIKE '52%' ORDER BY MO_NUMBER;

SELECT A.* FROM
(SELECT MO_NUMBER,MO_TYPE,MODEL_NAME,VERSION_CODE,TARGET_QTY,
            OUTPUT_QTY,ROUND(SYSDATE-MO_START_DATE)*24 MO_HOURS
FROM SFISM4.R_MO_BASE_T 
WHERE CLOSE_FLAG='2' AND MO_CREATE_DATE>SYSDATE-5 
AND MO_NUMBER LIKE '52%' ) A,
(SELECT DISTINCT MO_NUMBER FROM SFISM4.R_STATION_REC_T
WHERE  WORK_DATE = '20200429' ) B
WHERE A.MO_NUMBER=B.MO_NUMBER
ORDER BY A.MO_NUMBER;