oracle YYYYMMDDHH24MISS

Oracle中的日期格式化字符串YYYYMMDDHH24MISS可以用于将日期时间值格式化为指定格式的字符串。

例如，可以使用以下语句将当前日期时间格式化为YYYYMMDDHH24MISS格式的字符串：

SELECT TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') FROM DUAL;

这将返回类似于'20220118123456'的字符串，表示当前的年份、月份、日期、小时、分钟和秒数。

相关问题

UPDATE FPT_PRDLOTACTIVITYHISTORY t SET t.SumCycleTime = ( SELECT SUM(PH.EVENTTIME - ( SELECT /* +USE_HASH(A,PH) */ MAX(A.Eventtime) FROM MES_PRODUCTHISTORY A WHERE A.FACTORYNAME = PH.FACTORYNAME AND A.TIMEKEY < PH.TIMEKEY AND a.Productname = ph.Productname AND a.ProcessOperationName = ph.OldProcessOperationName AND a.EventName IN ('Release','Receive','TrackOut','Skip','ChangeOper','Rework') ) ) AS SumCycleTime FROM MES_PRODUCTHISTORY PH WHERE ph.factoryname = t.factoryname AND ph.LotName = t.LotName AND ph.Timekey = t.Timekey ) --, t.CycleTime = t.SumCycleTime / t.PRODUCTQUANTITY WHERE t.factoryname = 'ARRAY' AND t.Timekey >= '20230502220000' -- YYYYMMDDHH24MISS.HH AND t.Timekey < '20230502221000' -- YYYYMMDDHH24MISS.HH AND t.MappingEventName = 'Leave' AND nvl(t.Oldsubproductquantity,0) > 0 优化这个oracle sql

这个 SQL 语句可以进行以下优化：

1. 使用 EXISTS 替代子查询

可以将子查询改写为 EXISTS 子查询，因为 EXISTS 子查询只需要判断子查询是否返回结果，而不需要返回具体的值，比子查询更高效。

UPDATE FPT_PRDLOTACTIVITYHISTORY t
SET t.SumCycleTime = (
    SELECT SUM(PH.EVENTTIME - (
            SELECT /* +USE_HASH(A,PH) */
            MAX(A.Eventtime)
            FROM MES_PRODUCTHISTORY A
            WHERE A.FACTORYNAME = PH.FACTORYNAME
                AND A.TIMEKEY < PH.TIMEKEY
                AND a.Productname = ph.Productname
                AND a.ProcessOperationName = ph.OldProcessOperationName
                AND a.EventName IN ('Release','Receive','TrackOut','Skip','ChangeOper','Rework')
        )
    ) AS SumCycleTime
    FROM MES_PRODUCTHISTORY PH
    WHERE ph.factoryname = t.factoryname
        AND ph.LotName = t.LotName
        AND ph.Timekey = t.Timekey
        AND EXISTS (
            SELECT 1
            FROM MES_PRODUCTHISTORY A
            WHERE A.FACTORYNAME = PH.FACTORYNAME
                AND A.TIMEKEY < PH.TIMEKEY
                AND a.Productname = ph.Productname
                AND a.ProcessOperationName = ph.OldProcessOperationName
                AND a.EventName IN ('Release','Receive','TrackOut','Skip','ChangeOper','Rework')
        )
)
WHERE t.factoryname = 'ARRAY'
    AND t.Timekey >= '20230502220000'
    AND t.Timekey < '20230502221000'
    AND t.MappingEventName = 'Leave'
    AND NVL(t.Oldsubproductquantity,0) > 0;

2. 使用 JOIN 替代子查询

可以使用 JOIN 将子查询中的 MAX 函数改写为 JOIN 操作，从而提高查询效率。

UPDATE FPT_PRDLOTACTIVITYHISTORY t
SET t.SumCycleTime = (
    SELECT SUM(PH.EVENTTIME - A.MaxEventtime) AS SumCycleTime
    FROM MES_PRODUCTHISTORY PH
        INNER JOIN (
            SELECT FACTORYNAME, Productname, OldProcessOperationName, MAX(Eventtime) AS MaxEventtime
            FROM MES_PRODUCTHISTORY
            WHERE EventName IN ('Release','Receive','TrackOut','Skip','ChangeOper','Rework')
            GROUP BY FACTORYNAME, Productname, OldProcessOperationName
        ) A ON PH.FACTORYNAME = A.FACTORYNAME
            AND PH.Productname = A.Productname
            AND PH.OldProcessOperationName = A.OldProcessOperationName
            AND PH.Eventtime > A.MaxEventtime
    WHERE ph.factoryname = t.factoryname
        AND ph.LotName = t.LotName
        AND ph.Timekey = t.Timekey
)
WHERE t.factoryname = 'ARRAY'
    AND t.Timekey >= '20230502220000'
    AND t.Timekey < '20230502221000'
    AND t.MappingEventName = 'Leave'
    AND NVL(t.Oldsubproductquantity,0) > 0;

以上两种优化方式都可以有效提高 SQL 查询效率。

import cx_Oracle import pandas as pd import openpyxl #连接数据库 conn = cx_Oracle.connect('資料數據庫') sql="SELECT * FROM (SELECT * FROM ( SELECT NVL(E.locdesc,'') LOCATION,D.LINE,COUNT(*) QTY FROM REPAIR@CQABNB02 A,MO_D B,ISN C,MO_RUTE D,location E WHERE E.CODE=A.PLACE AND A.ISN=B.ISN AND B.ISN=C.ISN AND C.ISN=D.ISN AND D.SQ1=1 AND D.LIE IN('BN01B','BN02','BN09','BN10'，'BN11','BN12','BN13''BN14''BN15''BN16') AND A.RTIME BETWEEN TO_DATE('20210910200000','YYYYMMDDHH24MISS')AND TO_DATE('20210911070059','YYYYMMDDHH24MISS') GROUP BY ROLLUP(A.PLACE,D.LINE,E.locdesc)) PIVOT (SUM(QTY) FOR LINE IN ('BN01B','BN02','BN09','BN10'，'BN11','BN12','BN13''BN14''BN15''BN16'))) ORDER BY LOCATION" data=pd.read_sql(sql,conn) writer=pd.ExcelWriter('20210910.xlsx') #filename为导excel的路径 data.to_excel(writer,'sheet',index=False) writer.save() conn.close()

这是一个Python脚本，用于连接Oracle数据库，查询指定的数据，并将查询结果导出到Excel文件中。脚本中使用了cx_Oracle、pandas和openpyxl等Python库。其中，cx_Oracle库用于连接Oracle数据库，pandas库用于处理数据，openpyxl库用于导出Excel文件。你提供的脚本中查询的是某个时间段内，指定生产线和位置的维修数量统计结果，并按位置进行了排序。