xugu_license/支持库查询语句.sql

--oa库查询------------------------------------------------------
SELECT
	fmd.id 'id',
  	fm.REQUESTID '项目申请单id',
  	wr.REQUESTNAME AS '申请流程人名',
    wr.REQUESTNAMENEW  AS '申请流程人名',
	wr.REQUESTNAMEHTMLNEW AS '申请流程人名',
	fm.glxm AS '关联项目ID',
	PP.NAME AS '关联项目名',
    fm.SQSJ AS '申请日期', 
    hrm1.LASTNAME AS '销售人员名字', 
    hrm2.LASTNAME AS '运维人员名字', 
    fm.XSJSYX AS '销售邮箱', 
    fm.JFJSYX AS '运维邮箱', 
    fm.SYDW AS '使用单位', 
    fm.XMXXMS AS '项目详细明细',
    fm.JDS AS '总节点',
    fmd.JDS AS '节点数', 
    ws1.SELECTVALUE AS '产品编号', 
    ws1.SELECTNAME AS '产品名称', 
    ws2.SELECTNAME AS '产品版本', 
    fmd.CLQ AS 'cpu', 
    fmd.CZXT AS '操作系统', 
    fmd.IP AS '主mac', 
    fmd.MAC AS '副mac',
	wr.CREATEDATE  AS '创建日期',
	wr.CREATETIME  AS '创建时间',
	wr.LASTOPERATEDATE  AS '最后一次操作日期',
	wr.LASTOPERATETIME  AS '最后一次操作时间'

FROM formtable_main_146 fm
LEFT JOIN XUGU.HRMRESOURCE hrm1
ON TO_NUMBER(fm.XSRY)  = hrm1.id 
LEFT JOIN XUGU.HRMRESOURCE hrm2 
ON TO_NUMBER(fm.jfry)  = hrm2.id
LEFT JOIN XUGU.FORMTABLE_MAIN_146_dt1 fmd
ON fmd.mainid = fm.id 
LEFT JOIN XUGU.WORKFLOW_SELECTITEM ws1
ON fmd.cpmc = ws1.SELECTVALUE AND ws1.FIELDID = 14627
LEFT JOIN XUGU.WORKFLOW_SELECTITEM ws2
ON fmd.BB = ws2.SELECTVALUE AND ws2.FIELDID = 14628
LEFT JOIN WORKFLOW_REQUESTBASE WR 
ON fm.REQUESTID = WR.REQUESTID 
LEFT JOIN PRJ_PROJECTINFO PP
ON fm.glxm = PP.ID 
parallel 8

--指定时间查询
SELECT 
    la.ID, la.Unique_ID, la.OA_REQUESTID, la.OA_REQUESTNAME, la.OA_REQUESTNAMENEW,
    la.OA_REQUESTNAMEHTMLNEW, la.OA_GLXMID ,la.OA_GLXMNAME ,la.OA_SQSJ, la.OA_SALESPERSONNAME, la.OA_XSJSYX,
    la.OA_OPERATIONSPERSONNAME, la.OA_JFJSYX, la.OA_SYDW, la.OA_XMXXMS, la.OA_JDS,
    la.OA_NODECOUNT, la.OA_PRODUCTCODE, la.OA_PRODUCTNAME, la.OA_PRODUCTVERSION,
    la.OA_CPU, la.OA_OPERATINGSYSTEM, la.OA_MAINMAC, la.OA_SECONDMAC, la.OA_CREATIONDATE,
    la.OA_CREATIONTIME, la.OA_LASTOPERATEDATE, la.OA_LASTOPERATETIME, la.capture_Time,
    la.del_Time, la.LAST_OPERATE_TIME,
    li.ID AS License_ID, li.License_UniqueID, li.License_Flage, li.lic1, li.lic2, li.Creator_generate
FROM 
    target_OA_license la
INNER JOIN 
    License_generate_Info li
ON 
    la.Unique_ID = li.License_UniqueID

WHERE TO_TIMESTAMP(la.OA_CREATIONDATE || ' ' || la.OA_CREATIONTIME, 'YYYY-MM-DD HH24:MI:SS') > TO_TIMESTAMP('2024-08-12 10:16:21.000 AD', 'YYYY-MM-DD HH24:MI:SS')
--LIMIT 10 OFFSET 0;

-------------查询所有lic数据----------------------------------------------------------------------------------------
SELECT 
			la.ID, la.Unique_ID, la.OA_ID, la.OA_REQUESTID, la.OA_REQUESTNAME, la.OA_REQUESTNAMENEW,
			la.OA_REQUESTNAMEHTMLNEW, la.OA_GLXMID ,la.OA_GLXMNAME,
			la.OA_SQSJ, la.OA_SALESPERSONNAME, la.OA_XSJSYX,
			la.OA_OPERATIONSPERSONNAME, la.OA_JFJSYX, la.OA_SYDW, la.OA_XMXXMS, la.OA_JDS,
			la.OA_NODECOUNT, la.OA_PRODUCTCODE, la.OA_PRODUCTNAME, la.OA_PRODUCTVERSION,
			la.OA_CPU, la.OA_OPERATINGSYSTEM, la.OA_MAINMAC, la.OA_SECONDMAC, la.OA_CREATIONDATE,
			la.OA_CREATIONTIME, la.OA_LASTOPERATEDATE, la.OA_LASTOPERATETIME, la.capture_Time,
			la.del_Time, la.LAST_OPERATE_TIME,
			li.ID AS License_ID, li.OA_ID, li.License_UniqueID, li.License_Flage, li.lic1, li.lic2, li.Creator_generate
		FROM 
			target_OA_license la
		INNER JOIN 
			License_generate_Info li
		ON 
			la.Unique_ID = li.License_UniqueID
		WHERE 
			la.del_Time IS NULL
		LIMIT ? OFFSET ?
    
---------按OA_REQUESTID分组查询------------
SELECT     la.ID, la.Unique_ID, la.OA_REQUESTID, la.OA_REQUESTNAME, la.OA_REQUESTNAMENEW,
            la.OA_REQUESTNAMEHTMLNEW,
			la.OA_GLXMID ,la.OA_GLXMNAME,
			la.OA_SQSJ, la.OA_SALESPERSONNAME, la.OA_XSJSYX,
            la.OA_OPERATIONSPERSONNAME, la.OA_JFJSYX, la.OA_SYDW, la.OA_XMXXMS, la.OA_JDS,
            la.OA_NODECOUNT, la.OA_PRODUCTCODE, la.OA_PRODUCTNAME, la.OA_PRODUCTVERSION,
            la.OA_CPU, la.OA_OPERATINGSYSTEM, la.OA_MAINMAC, la.OA_SECONDMAC, la.OA_CREATIONDATE,
            la.OA_CREATIONTIME, la.OA_LASTOPERATEDATE, la.OA_LASTOPERATETIME, la.capture_Time,
            la.del_Time, la.LAST_OPERATE_TIME,
            li.ID AS License_ID, li.License_UniqueID, li.License_Flage, li.lic1, li.lic2, li.Creator_generate
FROM SYSDBA.TARGET_OA_LICENSE  la 
INNER JOIN 
			License_generate_Info li
		ON 
			la.Unique_ID = li.License_UniqueID
		WHERE OA_REQUESTID IN(SELECT OA_REQUESTID FROM (SELECT DISTINCT OA_REQUESTID FROM TARGET_OA_LICENSE) a
								WHERE ROWNUM BETWEEN 1 AND 10) 
		AND la.del_Time IS NULL