这样的SQL不想改第二遍更不想再写第二遍

最新推荐文章于 2021-06-24 17:32:17 发布
最新推荐文章于 2021-06-24 17:32:17 发布
阅读量401 收藏
点赞数
分类专栏： 基础篇 文章标签： sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_35807697/article/details/77949396
版权

作为一个后台开发写数据报表是很平常的事情，但要写一个统计整个系统的东西还把两个东西合到一起你就应该有多恶心（关键是有些数据还不是按照规律）。还要在上面的情形下，不断的加数据改东西。说了这么多可能还是因为我自己太菜吧，不说了上SQL。

WITH FILEDOC AS
 (SELECT BOD.FILENAME,
         TO_CHAR(BOD.BO_DOC_BODY_ID) BO_DOC_BODY_ID,
         BUS.BUSINESSOBJ_TYPE,
         TO_CHAR(BUS.BUSINESSOBJ_ID) BUSINESSOBJ_ID,
         DECODE(BUS.BUSINESSOBJ_TYPE,
                '需求文件',
                BUS.BUSINESSOBJ_ID || '1',
                '技术规范书',
                BUS.BUSINESSOBJ_ID || '2',
                '工作量文件',
                BUS.BUSINESSOBJ_ID || '3',
                '可研委托',
                BUS.BUSINESSOBJ_ID || '0',
                '可研文本/项目建议书',
                BUS.BUSINESSOBJ_ID || '1',
                '可研会审文档',
                BUS.BUSINESSOBJ_ID || '2',
                '需求上会材料',
                BUS.BUSINESSOBJ_ID || 'XQSH',
                '初步设计变更批复',
                BUS.BUSINESSOBJ_ID || 'CBSJPF',
                '一阶段设计变更批复',
                BUS.BUSINESSOBJ_ID || 'YJDBGPF',
                '初步设计变更批复',
                BUS.BUSINESSOBJ_ID || 'CBSJPF',
                '竣工资料',
                BUS.BUSINESSOBJ_ID || 'JGJS',
                '可研变更申请',
                BUS.BUSINESSOBJ_ID || '1',
                '采购附件',
                BUS.BUSINESSOBJ_ID || 'CGFJ',
                '物资附件',
                BUS.BUSINESSOBJ_ID || 'WZFJ',
                TO_CHAR(BUS.BUSINESSOBJ_ID)) BUSINESSOBJ_ID1,
         BOD.CREATORID,
         BUS.EXTEND_1,
         (SELECT V.FULLNAME FROM V_USER_ALL V WHERE V.USERID = BOD.CREATORID) FULLNAME,
         TO_CHAR(BOD.CREATIONDATE, 'YYYY-MM-DD') CREATIONDATE,
         TO_CHAR(BOD.BO_DOCUMENT_ID) BO_DOCUMENT_ID,
         '手工上传' DOCFROM
    FROM BO_DOCUMENT DOC, BO_DOC_BUSINESSOBJ BUS, BO_DOC_BODY BOD
   WHERE DOC.BO_DOCUMENT_ID = BUS.BO_DOCUMENT_ID
     AND BOD.BO_DOCUMENT_ID = BUS.BO_DOCUMENT_ID
     AND DOC.DELETE_FLAG = '2'),
P_ AS
 (SELECT T.*, RP.GATHERREQUIREMENTID
    FROM BO_PROJECT T, BO_PROJECT_TO_REQUIREMENT RP
   WHERE T.ID = RP.PROJECTID(+)
     AND T.ID = 198268298),
OADOC AS
 (SELECT T.ID,
         T.VERSION,
         T.TITLE,
         T.APPID,
         SUBSTR(T.APPLYDATE, 0, 10) APPLYDATE,
         T.STATUS,
         T.PMBIZCODE,
         T.PMBIZTYPE,
         T.OACODE,
         T.OATITLE,
         T.OADOCUMENTCODE,
         T.OADOCUMENTHYPERLINK,
         DECODE(T.OASTATE, 1, '审批中', 2, '审批完成') OASTATE,
         SUBSTR(OAPUBLISHDATE, 0, 10) OAPUBLISHDATE,
         FILETYPE,
         BT.DATANAME OA_FILETYPE,
         T.UPLOADTYPE,
         T.PMID,
         P.PROJECTNAME,
         P.PARENTID,
         P.ID PROJECTID,
         'OA系统' DOCFROM
    FROM PM_OADATAPROTOCOL_PM T,
         P_ P,
         (SELECT * FROM BO_BASEDATA T WHERE T.DATATYPE = 81) BT
   WHERE (T.PROJECTID = 198268298 OR T.PMBIZCODE = P.GATHERREQUIREMENTID OR
         T.PROJECTID IN (SELECT T.GATHERREQUIREMENTID
                            FROM BO_PROJECT_TO_REQUIREMENT T
                           WHERE T.PROJECTID = 198268298))
     AND T.OASTATE != 3
     AND T.FILETYPE = BT.DATACODE
     AND (T.PROJECTID = TO_CHAR(P.ID) OR T.PMBIZCODE = P.GATHERREQUIREMENTID)),
JDN AS
 (SELECT 'i_1_1_2' JDNAME, TO_CHAR(T.GATHERREQUIREMENTID) || '1' BUSINESSID
    FROM BO_GATHERREQUIREMENT T
   WHERE T.PROJECTID = 198268298
     AND T.STYLE = 'GATHER'
  UNION
  SELECT 'i_1_1_4' JDNAME, TO_CHAR(T.GATHERREQUIREMENTID) || '1' BUSINESSID
    FROM BO_GATHERREQUIREMENT T
   WHERE T.PROJECTID = 198268298
     AND T.STYLE = 'GATHER_SUM'
  UNION
  SELECT 'i_1_1_6' JDNAME, TO_CHAR(T.GATHERREQUIREMENTID) || '2' BUSINESSID
    FROM BO_GATHERREQUIREMENT T
   WHERE T.PROJECTID = 198268298
     AND T.STYLE = 'GATHER_REVIEW'
  UNION
  SELECT 'i_1_2_1' JDNAME, TO_CHAR(T.GATHERREQUIREMENTID) || '3' BUSINESSID
    FROM BO_GATHERREQUIREMENT T
   WHERE T.PROJECTID = 198268298
     AND T.STYLE = 'WORKLOAD_SUM'
  UNION
  SELECT 'i_1_2_3' JDNAME, TO_CHAR(T.GATHERREQUIREMENTID) || '3' BUSINESSID
    FROM BO_GATHERREQUIREMENT T
   WHERE T.PROJECTID = 198268298
     AND T.STYLE = 'WORKLOAD_EVALATE'
  UNION
  SELECT 'i_1_2_5' JDNAME, TO_CHAR(T.GATHERREQUIREMENTID) || '3' BUSINESSID
    FROM BO_GATHERREQUIREMENT T
   WHERE T.PROJECTID = 198268298
     AND T.STYLE = 'WORKLOAD'
  UNION
  SELECT 'i_1_3_2' JDNAME, TO_CHAR(T.ID) BUSINESSID
    FROM BO_SCHEMEEXCHANGE T
   WHERE T.PROJECTID = 198268298
  UNION
  SELECT 'i_1_3_4' JDNAME, TO_CHAR(T.ID) BUSINESSID
    FROM BO_SCHEMEDELIVERY T
   WHERE T.PROJECTID = 198268298
  UNION
  SELECT 'i_1_3_6' JDNAME, TO_CHAR(T.ID) BUSINESSID
    FROM BO_SCHEMEMEETING T
   WHERE T.PROJECTID = 198268298
  UNION
  SELECT 'i_2_1_2' JDNAME, TO_CHAR(T.WORKFLOWSTEPID) || '0' BUSINESSID
    FROM BO_DESIGNCONSIGN T
   WHERE T.DESIGNDEPT IS NOT NULL
     AND T.PROXYTYPE IN (2, 3)
     AND T.PLANPROJECTID = 198268298
  UNION
  SELECT 'i_2_1_2' JDNAME, TO_CHAR(D.BINDID) || '0' BUSINESSID
    FROM BO_DESIGNCONSIGN D, BO_SUPPLIER_INFO BSI
   WHERE TO_CHAR(D.DESIGNDEPT) = TO_CHAR(BSI.BO_SUPPLIER_INFO_ID(+))
     AND D.PLANPROJECTID = 198268298
     AND PROXYTYPE IN (2, 3)
     AND DESIGNDEPT IS NULL
  UNION
  SELECT 'i_2_2_2' JDNAME, TO_CHAR(T.ID) || '1' BUSINESSID
    FROM BO_PROJECTFEASI T
   WHERE T.DELIVERYDATE IS NOT NULL
     AND T.PROJECTID = 198268298
  UNION
  SELECT 'i_2_3_2' JDNAME, TO_CHAR(T.ID) || '2' BUSINESSID
    FROM BO_PROJECTFEASI T
   WHERE T.JUDGEDATE IS NOT NULL
     AND T.PROJECTID = 198268298
  UNION
  SELECT 'i_2_3_4' JDNAME, TO_CHAR(T.BO_FEASI_C_APPLY_ID) || '1' BUSINESSID
    FROM BO_FEASI_C_APPLY T
   WHERE T.PROJECTID = 198268298
  UNION
  SELECT 'i_2_3_6' JDNAME, TO_CHAR(T.BO_FEASI_C_APPLY_ID) BUSINESSID
    FROM BO_FEASI_C_APPLY T
   WHERE T.PROJECTID = 198268298
  UNION
  SELECT 'i_3_1_1' JDNAME, TO_CHAR(T.ID) || 'XQSH' BUSINESSID
    FROM BO_PROJECT T
   WHERE T.ID = 198268298
  UNION
  SELECT 'i_4_1_2' JDNAME, TO_CHAR(D.BINDID) BUSINESSID
    FROM BO_DESIGNCONSIGN D, BO_SUPPLIER_INFO BSI
   WHERE TO_CHAR(D.DESIGNDEPT) = TO_CHAR(BSI.BO_SUPPLIER_INFO_ID(+))
     AND D.PLANPROJECTID = 198268298
     AND PROXYTYPE IN (1, 3)
     AND DESIGNDEPT IS NULL
  UNION
  SELECT 'i_4_2_2' JDNAME, TO_CHAR(T.PMS_DESIGN_SURVEY_ID) BUSINESSID
    FROM PMS_PRJ_DESIGN_SURVEY T
   WHERE T.PROJECT_ID = 198268298
  UNION
  SELECT 'i_4_4_6' JDNAME, TO_CHAR(T.BO_DESIGNONE_ID) BUSINESSID
    FROM BO_DESIGNONE T
   WHERE T.DESGIGNDRAWINGTYPE = 1
     AND T.PROJECTID = 198268298
  UNION
  SELECT 'i_4_4_6' JDNAME,
         TO_CHAR(T.BO_DESIGNREPLY_SUB_ID) || 'YJDBGPF' BUSINESSID
    FROM BO_DESIGNREPLY_SUB T
   WHERE T.CONSTRUCTTYPE = 4
     AND T.PROJECTID = 198268298
  UNION
  SELECT 'i_4_4_6' JDNAME, TO_CHAR(T.BO_DESGIGN_INITIAL_ID) BUSINESSID
    FROM BO_DESGIGN_INITIAL T
   WHERE T.PROJECTID = 198268298
  UNION
  SELECT 'i_4_4_6' JDNAME,
         TO_CHAR(T.BO_DESIGNREPLY_SUB_ID) || 'CBSJPF' BUSINESSID
    FROM BO_DESIGNREPLY_SUB T
   WHERE T.CONSTRUCTTYPE = 6
     AND T.PROJECTID = 198268298
  UNION
  SELECT 'i_4_5_6' JDNAME, TO_CHAR(T.BO_DESGIGN_INITIAL_ID) BUSINESSID
    FROM BO_DESGIGN_INITIAL T
   WHERE T.PROJECTID = 198268298
  UNION
  SELECT 'i_4_5_6' JDNAME,
         TO_CHAR(T.BO_DESIGNREPLY_SUB_ID) || 'CBSJPF' BUSINESSID
    FROM BO_DESIGNREPLY_SUB T
   WHERE T.CONSTRUCTTYPE = 6
     AND T.PROJECTID = 198268298
  UNION
  SELECT 'i_4_6_6' JDNAME, TO_CHAR(T.BO_DESIGNREPLY_SUB_ID) BUSINESSID
    FROM BO_DESIGNREPLY_SUB T
   WHERE T.DESGIGNDRAWINGTYPE = 2
     AND T.PROJECTID = 198268298
  UNION
  SELECT 'i_4_6_6' JDNAME, TO_CHAR(T.BO_DESIGNONE_C_APPLY_ID) BUSINESSID
    FROM BO_DESIGNONE_C_APPLY T
   WHERE T.CONSTRUCTTYPE = 1
     AND T.PROJECTID = 198268298
  UNION
  SELECT 'i_4_6_6' JDNAME, TO_CHAR(T.BO_DESIGNREPLY_SUB_ID) BUSINESSID
    FROM BO_DESIGNREPLY_SUB T
   WHERE T.CONSTRUCTTYPE = 4
     AND T.PROJECTID = 198268298
  UNION
  SELECT 'i_5_1_2' JDNAME, TO_CHAR(T.ID) || '38CGFJ' BUSINESSID
    FROM BO_PROJECT T
   WHERE T.ID = 198268298
  UNION
  SELECT 'i_6_1_2' JDNAME, TO_CHAR(T.ID) || '38WZFJ' BUSINESSID
    FROM BO_PROJECT T
   WHERE T.ID = 198268298
  UNION
  SELECT 'i_7_1_1' JDNAME, TO_CHAR(T.BO_QUALITY_REPORT_ID) BUSINESSID
    FROM BO_QUALITY_REPORT T
   WHERE T.PROJECTID = 198268298
  UNION
  SELECT 'i_7_1_2' JDNAME, TO_CHAR(T.BO_QUALITY_NOTICE_ID) BUSINESSID
    FROM BO_QUALITY_NOTICE T
   WHERE T.PROJECTID = 198268298
  UNION
  SELECT 'i_7_1_4' JDNAME, TO_CHAR(T.BO_QUALITY_NOTICE_ID) BUSINESSID
    FROM BO_QUALITY_NOTICE T
   WHERE T.PROJECTID = 198268298
  UNION
  SELECT 'i_7_1_4' JDNAME, TO_CHAR(T.BO_QUALITY_REPORT_ID) BUSINESSID
    FROM BO_QUALITY_REPORT T
   WHERE T.PROJECTID = 198268298
  UNION
  SELECT 'i_7_2_1' JDNAME, TO_CHAR(T.BO_CONSTRUCTION_S_INFO_ID) BUSINESSID
    FROM BO_CONSTRUCTION_S_INFO T, BO_CONSTRUCTION_START T2
   WHERE T.BO_CONSTRUCTION_START_ID = T2.BO_CONSTRUCTION_START_ID
     AND T2.AUDITSTATE != '1'
     AND T2.PROJECTID = 198268298
  UNION
  SELECT 'i_7_3_1' JDNAME,
         TO_CHAR(WMSYS.WM_CONCAT(T.GATHERREQUIREMENTID)) BUSINESSID
    FROM BO_GATHERREQUIREMENT T, BO_PROJECT P
   WHERE T.PROJECTID = P.ID
     AND T.PROJECTID = 198268298
     AND T.STYLE = 'CONSTRUCTION'
  UNION
  SELECT 'i_7_4_1' JDNAME, TO_CHAR(T.BO_RESOURCE_REQUEST_ID) BUSINESSID
    FROM BO_RESOURCE_REQUEST T
   WHERE T.PROJECTID = 198268298
  UNION
  SELECT 'i_7_5_2' JDNAME, TO_CHAR(P.BO_PROJECT_PAUSE_APPLY_ID) BUSINESSID
    FROM BO_PRO_PROJECT_PAUSE_APPLY PR,
         BO_PROJECT_PAUSE_APPLY     P,
         V_USER_ORG                 V
   WHERE PR.BO_PROJECT_PAUSE_APPLY_ID = P.BO_PROJECT_PAUSE_APPLY_ID
     AND P.CREATE_USERID = V.USERID
     AND PR.PROJECTID = 198268298
  UNION
  SELECT 'i_7_6_2' JDNAME, TO_CHAR(P.BO_PROJECT_RESUME_APPLY_ID) BUSINESSID
    FROM BO_PRO_PROJECT_RESUME_APPLY PR,
         BO_PROJECT_RESUME_APPLY     P,
         V_USER_ORG                  V
   WHERE PR.BO_PROJECT_RESUME_APPLY_ID = P.BO_PROJECT_RESUME_APPLY_ID
     AND P.CREATE_USERID = V.USERID
     AND PR.PROJECTID = 198268298
  UNION
  SELECT 'i_7_7_1' JDNAME, TO_CHAR(T.BO_CONSTRUCTION_TEST_ID) BUSINESSID
    FROM BO_CONSTRUCTION_TEST T
   WHERE T.PROJECTID = 198268298
  UNION
  SELECT 'i_7_8_1' JDNAME, TO_CHAR(T.BO_CONSTRUCTION_ONLINE_ID) BUSINESSID
    FROM BO_CONSTRUCTION_ONLINE T
   WHERE T.PROJECTID = 198268298
  UNION
  SELECT 'i_7_9_1' JDNAME, TO_CHAR(T.BO_CONSTRUCTION_C_INFO_ID) BUSINESSID
    FROM BO_CONSTRUCTION_C_INFO T, BO_CONSTRUCTION_COMPLETION T2
   WHERE T.BO_CONSTRUCTION_COMPLETION_ID = T2.BO_CONSTRUCTION_COMPLETION_ID
     AND T2.AUDITSTATE != '1'
     AND T2.PROJECTID = 198268298
  UNION
  SELECT 'i_8_1_1' JDNAME, TO_CHAR(T.BO_TEC_INSPECTION_ID) BUSINESSID
    FROM BO_TEC_INSPECTION T
   WHERE T.PROJECTID = 198268298
  UNION
  SELECT 'i_8_3_2' JDNAME, TO_CHAR(T.ID) BUSINESSID
    FROM BO_PROJECT T
   WHERE T.ID = 198268298
  UNION
  SELECT 'i_8_3_2' JDNAME, TO_CHAR(T.ID) || 'JGJS' BUSINESSID
    FROM BO_PROJECT T
   WHERE T.ID = 198268298
  UNION
  SELECT 'i_8_4_3' JDNAME, TO_CHAR(T.BINDID) BUSINESSID
    FROM BO_PROJECTEXAMINE T
   WHERE T.PROJECTID = 198268298
  UNION
  SELECT 'i_8_4_3' JDNAME, TO_CHAR(T.BINDID) || '1' BUSINESSID
    FROM BO_PROJECTEXAMINE T, BO_PROJECT BP
   WHERE PROJECTID = 198268298
     AND PROJECTID = BP.ID
  UNION
  SELECT 'i_8_5_1' JDNAME, TO_CHAR(B.ID) BUSINESSID
    FROM BO_PROJECTARCHIVE B, V_USER V
   WHERE B.PROJECTID = 198268298
     AND B.CREATEUSER = V.LOGINNAME
  UNION
  SELECT 'i_8_6_1' JDNAME, TO_CHAR(BO_CONSTRUCTION_RUNNING_ID) BUSINESSID
    FROM BO_CONSTRUCTION_RUNNING T
   WHERE T.PROJECTID = 198268298
  UNION
  SELECT 'i_8_7_2' JDNAME, TO_CHAR(T.ID) || 'JGJS' BUSINESSID
    FROM BO_PROJECT T
   WHERE T.ID = 198268298
  UNION
  SELECT 'i_8_8_3' JDNAME, TO_CHAR(T.BINDID) || '2' BUSINESSID
    FROM BO_PROJECTEXAMINE T
   WHERE T.PROJECTID = 198268298
  UNION
  SELECT 'i_8_8_3' JDNAME, TO_CHAR(T.BINDID) || '2' BUSINESSID
    FROM BO_PROJECTEXAMINE T, BO_PROJECT BP
   WHERE PROJECTID = 198268298
     AND PROJECTID = BP.ID
  UNION
  SELECT 'i_8_9_1' JDNAME,
         TO_CHAR(BO_COMPLETION_RECORD_ID) || '1' BUSINESSID
    FROM BO_COMPLETION_RECORD T
   WHERE T.PROJECTID = 198268298
  UNION
  SELECT 'i_8_9_2' JDNAME, TO_CHAR(BO_COMPLETION_NOTICE_ID) BUSINESSID
    FROM BO_COMPLETION_NOTICE T
   WHERE T.PROJECTID = 198268298
  UNION
  SELECT 'i_8_9_3' JDNAME, TO_CHAR(BO_COMPLETION_RECORD_ID) BUSINESSID
    FROM BO_COMPLETION_RECORD T
   WHERE T.PROJECTID = 198268298
  UNION
  SELECT 'i_9_1_2' JDNAME, TO_CHAR(T.BO_PROJECTAFTEREVALUTE_ID) BUSINESSID
    FROM BO_PROJECTAFTEREVALUTE T
   WHERE T.PROJECTID = 198268298),
JDNE AS
 (SELECT *
    FROM BO_ITFILE_PHASE
  MINUS
  SELECT *
    FROM BO_ITFILE_PHASE B
   WHERE B.BO_ITFILE_PHASE_ID LIKE 'i_4_4%'
      OR B.BO_ITFILE_PHASE_ID LIKE 'i_4_5_3'
      OR B.BO_ITFILE_PHASE_ID LIKE 'i_4_5_4'
      OR B.BO_ITFILE_PHASE_ID LIKE 'i_4_6_3'
      OR B.BO_ITFILE_PHASE_ID LIKE 'i_4_6_4'),
JDNEOA AS
 (SELECT *
    FROM BO_ITFILE_PHASE
  MINUS
  SELECT *
    FROM BO_ITFILE_PHASE B
   WHERE B.BO_ITFILE_PHASE_ID LIKE 'i_4_4%'
      OR B.BO_ITFILE_PHASE_ID LIKE 'i_4_5_3'
      OR B.BO_ITFILE_PHASE_ID LIKE 'i_4_5_4'
      OR B.BO_ITFILE_PHASE_ID LIKE 'i_4_6_3'
      OR B.BO_ITFILE_PHASE_ID LIKE 'i_4_6_4'
      OR B.BO_ITFILE_PHASE_ID LIKE 'i_1_2_2'
      OR B.BO_ITFILE_PHASE_ID LIKE 'i_1_2_3'
      OR B.BO_ITFILE_PHASE_ID LIKE 'i_2_3_1'
      OR B.BO_ITFILE_PHASE_ID LIKE 'i_2_3_3'),
REFD AS
 (SELECT N.JDNAME, N.BUSINESSID, F.*
    FROM FILEDOC F, JDN N
   WHERE F.BUSINESSOBJ_ID1 = N.BUSINESSID)
SELECT R.*
  FROM (SELECT *
          FROM (SELECT D.BO_ITFILE_PHASE_ID BO_ID,
                       D.JIEDUAN,
                       D.JDNAME,
                       D.SEQ,
                       D.BITIAN,
                       F.FILENAME FILETITLE,
                       '' OADOCUMENTCODE,
                       '' OACODE,
                       F.BUSINESSOBJ_TYPE FILETYPE,
                       F.CREATIONDATE APPLYDATE,
                       '' OASTATE,
                       '' OAPUBLISHDATE,
                       F.FULLNAME CREATE_USER,
                       '手工上传' DOCFROM,
                       '1' TYPE_,
                       F.BO_DOC_BODY_ID,
                       F.BUSINESSOBJ_ID,
                       F.BO_DOCUMENT_ID,
                       F.CREATORID,
                       F.EXTEND_1,
                       '' ID_
                  FROM JDNE D, REFD F
                 WHERE D.BO_ITFILE_PHASE_ID = F.JDNAME(+)
                   AND D.BELONG = '1'
                UNION
                SELECT D.BO_ITFILE_PHASE_ID BO_ID,
                       D.JIEDUAN,
                       D.JDNAME,
                       D.SEQ,
                       D.BITIAN,
                       F.FILENAME FILETITLE,
                       '' OADOCUMENTCODE,
                       '' OACODE,
                       F.BUSINESSOBJ_TYPE FILETYPE,
                       F.CREATIONDATE APPLYDATE,
                       '' OASTATE,
                       '' OAPUBLISHDATE,
                       F.FULLNAME CREATE_USER,
                       '手工上传' DOCFROM,
                       '1' TYPE_,
                       F.BO_DOC_BODY_ID,
                       F.BUSINESSOBJ_ID,
                       F.BO_DOCUMENT_ID,
                       F.CREATORID,
                       F.EXTEND_1,
                       '' ID_
                  FROM JDNE D, REFD F
                 WHERE D.BO_ITFILE_PHASE_ID = F.JDNAME(+)
                   AND D.BELONG = '1'
                   AND F.BUSINESSOBJ_TYPE IN D.BUSINESSOBJ_TYPE
                UNION
                SELECT D.BO_ITFILE_PHASE_ID BO_ID,
                       D.JIEDUAN,
                       D.JDNAME,
                       D.SEQ,
                       D.BITIAN,
                       E.OATITLE,
                       E.OADOCUMENTCODE,
                       E.OACODE,
                       E.OA_FILETYPE,
                       E.APPLYDATE,
                       E.OASTATE,
                       E.OAPUBLISHDATE,
                       '' CREATE_USER,
                       E.DOCFROM,
                       '2' TYPE_,
                       '' BO_DOC_BODY_ID,
                       '' BUSINESSOBJ_ID,
                       '' BO_DOCUMENT_ID,
                       '' CREATORID,
                       '' EXTEND_1,
                       TO_CHAR(E.ID_) ID_
                  FROM JDNEOA D,
                       (SELECT D.BO_ITFILE_PHASE_ID BO_ID,
                               D.JIEDUAN,
                               D.JDNAME,
                               D.SEQ,
                               D.BITIAN,
                               OA.OATITLE,
                               OA.OADOCUMENTCODE,
                               OA.OACODE,
                               OA.OA_FILETYPE,
                               OA.APPLYDATE,
                               OA.OASTATE,
                               OA.OAPUBLISHDATE,
                               '' CREATE_USER,
                               DECODE(OA.UPLOADTYPE, '1', 'OA系统', '手工上传') DOCFROM,
                               '2' TYPE_,
                               '' BO_DOC_BODY_ID,
                               '' BUSINESSOBJ_ID,
                               '' BO_DOCUMENT_ID,
                               '' CREATORID,
                               '' EXTEND_1,
                               TO_CHAR(OA.ID) ID_
                          FROM JDNEOA D, OADOC OA
                         WHERE D.PMBIZTYPE = OA.PMBIZTYPE(+)
                           AND D.BELONG = '2'
                           AND D.OATYPE LIKE '%,' || OA.OA_FILETYPE || '%') E
                 WHERE D.BO_ITFILE_PHASE_ID = E.BO_ID(+)
                   AND D.BELONG = '2'
                UNION
                SELECT D.BO_ITFILE_PHASE_ID BO_ID,
                       D.JIEDUAN,
                       D.JDNAME,
                       D.SEQ,
                       D.BITIAN,
                       E.OATITLE,
                       E.OADOCUMENTCODE,
                       E.OACODE,
                       E.OA_FILETYPE,
                       E.APPLYDATE,
                       E.OASTATE,
                       E.OAPUBLISHDATE,
                       '' CREATE_USER,
                       E.DOCFROM,
                       '2' TYPE_,
                       '' BO_DOC_BODY_ID,
                       '' BUSINESSOBJ_ID,
                       '' BO_DOCUMENT_ID,
                       '' CREATORID,
                       '' EXTEND_1,
                       TO_CHAR(E.ID_) ID_
                  FROM JDNE D,
                       (SELECT D.BO_ITFILE_PHASE_ID BO_ID,
                               D.JIEDUAN,
                               D.JDNAME,
                               D.SEQ,
                               D.BITIAN,
                               OA.OATITLE,
                               OA.OADOCUMENTCODE,
                               OA.OACODE,
                               OA.OA_FILETYPE,
                               OA.APPLYDATE,
                               OA.OASTATE,
                               OA.OAPUBLISHDATE,
                               '' CREATE_USER,
                               DECODE(OA.UPLOADTYPE, '1', 'OA系统', '手工上传') DOCFROM,
                               '2' TYPE_,
                               '' BO_DOC_BODY_ID,
                               '' BUSINESSOBJ_ID,
                               '' BO_DOCUMENT_ID,
                               '' CREATORID,
                               '' EXTEND_1,
                               TO_CHAR(OA.ID) ID_
                          FROM (SELECT *
                                  FROM JDNE B
                                 WHERE B.BO_ITFILE_PHASE_ID = 'i_1_2_2') D,
                               OADOC OA
                         WHERE D.PMBIZTYPE = OA.PMBIZTYPE(+)
                           AND OA.OA_FILETYPE = '公司发文'
                           AND OA.PMBIZCODE =
                               (SELECT T.GATHERREQUIREMENTID
                                  FROM BO_GATHERREQUIREMENT T
                                 WHERE T.PROJECTID = 198268298
                                   AND T.STYLE = 'WORKLOAD_EVALATE')) E
                 WHERE D.BO_ITFILE_PHASE_ID = E.BO_ID(+)
                   AND D.BELONG = '2'
                   AND D.BO_ITFILE_PHASE_ID = 'i_1_2_2'
                UNION
                SELECT D.BO_ITFILE_PHASE_ID BO_ID,
                       D.JIEDUAN,
                       D.JDNAME,
                       D.SEQ,
                       D.BITIAN,
                       E.OATITLE,
                       E.OADOCUMENTCODE,
                       E.OACODE,
                       E.OA_FILETYPE,
                       E.APPLYDATE,
                       E.OASTATE,
                       E.OAPUBLISHDATE,
                       '' CREATE_USER,
                       E.DOCFROM,
                       '2' TYPE_,
                       '' BO_DOC_BODY_ID,
                       '' BUSINESSOBJ_ID,
                       '' BO_DOCUMENT_ID,
                       '' CREATORID,
                       '' EXTEND_1,
                       TO_CHAR(E.ID_) ID_
                  FROM JDNE D,
                       (SELECT D.BO_ITFILE_PHASE_ID BO_ID,
                               D.JIEDUAN,
                               D.JDNAME,
                               D.SEQ,
                               D.BITIAN,
                               OA.OATITLE,
                               OA.OADOCUMENTCODE,
                               OA.OACODE,
                               OA.OA_FILETYPE,
                               OA.APPLYDATE,
                               OA.OASTATE,
                               OA.OAPUBLISHDATE,
                               '' CREATE_USER,
                               DECODE(OA.UPLOADTYPE, '1', 'OA系统', '手工上传') DOCFROM,
                               '2' TYPE_,
                               '' BO_DOC_BODY_ID,
                               '' BUSINESSOBJ_ID,
                               '' BO_DOCUMENT_ID,
                               '' CREATORID,
                               '' EXTEND_1,
                               TO_CHAR(OA.ID) ID_
                          FROM (SELECT *
                                  FROM JDNE B
                                 WHERE B.BO_ITFILE_PHASE_ID = 'i_1_2_4') D,
                               OADOC OA
                         WHERE D.PMBIZTYPE = OA.PMBIZTYPE(+)
                           AND OA.PMBIZCODE =
                               (SELECT T.GATHERREQUIREMENTID
                                  FROM BO_GATHERREQUIREMENT T
                                 WHERE T.PROJECTID = 198268298
                                   AND T.STYLE = 'WORKLOAD')
                           AND OA.OA_FILETYPE = '公司发文') E
                 WHERE D.BO_ITFILE_PHASE_ID = E.BO_ID(+)
                   AND D.BELONG = '2'
                   AND D.BO_ITFILE_PHASE_ID = 'i_1_2_4'
                UNION
                SELECT D.BO_ITFILE_PHASE_ID BO_ID,
                       D.JIEDUAN,
                       D.JDNAME,
                       D.SEQ,
                       D.BITIAN,
                       E.OATITLE,
                       E.OADOCUMENTCODE,
                       E.OACODE,
                       E.OA_FILETYPE,
                       E.APPLYDATE,
                       E.OASTATE,
                       E.OAPUBLISHDATE,
                       '' CREATE_USER,
                       E.DOCFROM,
                       '2' TYPE_,
                       '' BO_DOC_BODY_ID,
                       '' BUSINESSOBJ_ID,
                       '' BO_DOCUMENT_ID,
                       '' CREATORID,
                       '' EXTEND_1,
                       TO_CHAR(E.ID_) ID_
                  FROM JDNE D,
                       (SELECT D.BO_ITFILE_PHASE_ID BO_ID,
                               D.JIEDUAN,
                               D.JDNAME,
                               D.SEQ,
                               D.BITIAN,
                               OA.OATITLE,
                               OA.OADOCUMENTCODE,
                               OA.OACODE,
                               OA.OA_FILETYPE,
                               OA.APPLYDATE,
                               OA.OASTATE,
                               OA.OAPUBLISHDATE,
                               '' CREATE_USER,
                               DECODE(OA.UPLOADTYPE, '1', 'OA系统', '手工上传') DOCFROM,
                               '2' TYPE_,
                               '' BO_DOC_BODY_ID,
                               '' BUSINESSOBJ_ID,
                               '' BO_DOCUMENT_ID,
                               '' CREATORID,
                               '' EXTEND_1,
                               TO_CHAR(OA.ID) ID_
                          FROM (SELECT *
                                  FROM JDNE B
                                 WHERE B.BO_ITFILE_PHASE_ID = 'i_2_3_1') D,
                               OADOC OA
                         WHERE D.PMBIZTYPE = OA.PMBIZTYPE(+)
                           AND OA.PMBIZCODE = '198268298'
                           AND OA.OA_FILETYPE = '会议纪要') E
                 WHERE D.BO_ITFILE_PHASE_ID = E.BO_ID(+)
                   AND D.BELONG = '2'
                   AND D.BO_ITFILE_PHASE_ID = 'i_2_3_1'
                UNION
                SELECT D.BO_ITFILE_PHASE_ID BO_ID,
                       D.JIEDUAN,
                       D.JDNAME,
                       D.SEQ,
                       D.BITIAN,
                       E.OATITLE,
                       E.OADOCUMENTCODE,
                       E.OACODE,
                       E.OA_FILETYPE,
                       E.APPLYDATE,
                       E.OASTATE,
                       E.OAPUBLISHDATE,
                       '' CREATE_USER,
                       E.DOCFROM,
                       '2' TYPE_,
                       '' BO_DOC_BODY_ID,
                       '' BUSINESSOBJ_ID,
                       '' BO_DOCUMENT_ID,
                       '' CREATORID,
                       '' EXTEND_1,
                       TO_CHAR(E.ID_) ID_
                  FROM JDNE D,
                       (SELECT D.BO_ITFILE_PHASE_ID BO_ID,
                               D.JIEDUAN,
                               D.JDNAME,
                               D.SEQ,
                               D.BITIAN,
                               OA.OATITLE,
                               OA.OADOCUMENTCODE,
                               OA.OACODE,
                               OA.OA_FILETYPE,
                               OA.APPLYDATE,
                               OA.OASTATE,
                               OA.OAPUBLISHDATE,
                               '' CREATE_USER,
                               DECODE(OA.UPLOADTYPE, '1', 'OA系统', '手工上传') DOCFROM,
                               '2' TYPE_,
                               '' BO_DOC_BODY_ID,
                               '' BUSINESSOBJ_ID,
                               '' BO_DOCUMENT_ID,
                               '' CREATORID,
                               '' EXTEND_1,
                               TO_CHAR(OA.ID) ID_
                          FROM (SELECT *
                                  FROM JDNE B
                                 WHERE B.BO_ITFILE_PHASE_ID = 'i_2_3_3') D,
                               OADOC OA
                         WHERE D.PMBIZTYPE = OA.PMBIZTYPE(+)
                           AND OA.PMBIZCODE IN
                               (SELECT T.BO_FEASI_C_APPLY_ID
                                  FROM BO_FEASI_C_APPLY T
                                 WHERE T.PROJECTID = 198268298)
                           AND OA.OA_FILETYPE = '公司发文') E
                 WHERE D.BO_ITFILE_PHASE_ID = E.BO_ID(+)
                   AND D.BELONG = '2'
                   AND D.BO_ITFILE_PHASE_ID = 'i_2_3_3') A
         ORDER BY A.SEQ, A.TYPE_ DESC) R
 WHERE 1 = 1

这上面的SQL是打印出来的SQL，上面还有很多嵌套判断。这里面不是一点点的恶心，可以看看里面有多少个临时表拼接来的结果。查询的速度那是慢的不行，所以这里面也没有考虑什么多少个子查询。

qq_35807697
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL 合并多行记录的相同字段值
12-15
１．从数据库中先查询符合条件的记录，存放于一个DataTable中,在使用c#等开始遍历这张表，利用DataRow中的主键，再去读取相应的符合条件的多条记录，合并这些第二次读取到的记录内容，返回给前面的这个DataRow数据行。这样做没有错，但是如果数据量大，我们可能面临无数次的打开断开数据库链接，速度效率将会很低。 2.从数据库中一次读取数据到一张表中返回并显示到UI层。说起来谁都想这么做，但是以前太笨，没有去研究这个，今天因为数据量较大的原因，让我不得不想些其他办法来提高点效率。 Google~hk一下，果真有答案，然后依葫芦画瓢，自己写了一个 目的是获取不定量的符合条件的兼职记录，并将每
有了这4款工具，再也不怕写SQL了，让你写出一手优雅的SQL
emprere的博客
02-22 6691
作者：老王谈运维来源：http://suo.im/6q5FkS对于正在运行的mysql，性能如何，参数设置的是否合理，账号设置的是否存在安全隐患，你是否了然于胸呢？俗话说工欲善其事，必先...
一个炫酷的个人网站带后台
qq_41935885的博客
07-26 6648
前段时间觉得自己学的挺杂的,h5,css,js,jquary,ajax什么的，干脆就全部整合一起做了个小demo，算是一个综合性的练习。 这个项目基于h5+css+js+jquary+ajax+java+springboot+mybatis。没有用响应式框架，只能兼容oc端，自己做的ui，有点丑，话不多说，上图。 这是登录页面 注册页面 参观地址：http://47.103.24.88 游客账...
初学者不想写SQL？来我教你写一个自动生成！
FNTP_
08-05 4392
对于刚学习SQL的同学来说，写SQL代码莫过于是一件很痛苦的事，在你没有学习框架技术的时候，你不知道怎么处理繁杂的SQL语句的时候，你很头痛，你不想写一堆无意义的代码，哎，没事，我们今天花十分钟，基于Java手写一个，SQL语句自动生成！ 好，废话不多说，上画面： 怎么实现的呢？其实很简单： 实现这个功能，你只需要掌握JDBC的基本手写实现(在不使用框架的基础之上)。 首先，你需要一个集成的JDBC(这里是手写的，所以大家可以根据自己的需求按需求写代码) 注意这里的版本切换，mysql5.X与mys.
mysql:基础总结
yubin1285570923的博客
06-24 588
一 常用命令 C:\Windows\system32>net stop mysql0815 MySQL0815 服务正在停止. MySQL0815 服务已成功停止。 C:\Windows\system32>net start mysql0815 MySQL0815 服务正在启动 . MySQL0815 服务已经启动成功。 mysql -h localhost -P 3306 -u root -p mysql -h localhost -P 3306 -u root -proot mysql -u
docker学习
qq_41935885的博客
05-12 111
背景： 开发与运维之间因为环境不同而导致的矛盾。 集群环境下每台机器部署相同应用。 Docker架构 Docker使用C/S架构，Client通过接口与Servers进程通信实现容器的构建，运行和发布。 Docker的安装 ...
SQL性能优化
12-20
dj及xh_bz的比较，而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。  查询表顺序的影响  在FROM后面的表中的列表顺序会对SQL执行性能影响，在...
经典SQL语句大全
12-20
3),例如：在一个外部表中导入数据，由于某些原因第一次只导入了一部分，但很难判断具体位置，这样只有在下一次全部导入，这样也就产生好多重复的字段，怎样删除重复字段 alter table tablename --添加一个自增列 add...
Qt Creator 的安装和hello world 程序+其他程序的编写--不是一般的好
01-28
第二个按钮我们自己写了槽函数语句，其实图形的设计与直接写代码效果是 一样的。 这个程序里我们实现了两类窗口打开的方式，一个是自身消失而 后打开另一个窗口，一个是打开另一个窗口而自身不消失。可以看到他们...
经纬度的正则表达式
热门推荐
qq_35807697的博客
09-04 1万+
经度的范围是0-180，保留6位小数的正则表达式：”^(([1-9]\d?)|(1[0-7]\d))(\.\d{1,6})|180|0(\.\d{1,6})?”维度的范围是0−90，保留6位小数的正则表达式：”(([1−8]\d?)|([1−8]\d))(\.\d1,6)|90|0(\.\d1,6)?” 维度的范围是0-90，保留6位小数的正则表达式：”^(([1-8]\d?)|([1-8]\d
Oracle数据库多重子查询嵌套的坑
qq_35807697的博客
05-25 5446
在Oracle数据库中多重子查询的使用代码如下:SELECT T.*, ROWNUM RN FROM (SELECT B.*, NVL(A.AMOUNT, 0) AMOUNT, V.FULLNAME, DECODE(SHARE_TYPE, 1, '***', 0, '%%%') SHARE_TYPE
ftp服务器和sftp服务器
qq_35807697的博客
04-18 4573
首先，ftp跟sftp是一种传输协议，一种安全，一种不安全。其中经过实践发现要有相对于的服务器才能实现上传跟下载，不能混淆使用。 windows中ftp服务器的建立方法：第一种：系统自带的ftp 开启服务： 建立ftp，映射地址和文件夹 新建用户和密码访问ftp 贴上java类的测试代码： package com.test; import java.io.File; ...
页面打包下载功能(包括页面上带的附件)
qq_35807697的博客
06-13 2813
需求描述 用户浏览页面查看信息，点击按钮下载页面内容并包括页面所带附件的插件打包效果图如下： 这里特别说一下，下载的页面与原页面是一样的。（包含页面的样式也一并下载）      2. 代码展示   要想把页面下载打包首先要把整个页面的内容包括样式图片下载到临时文件，但是页面的内容怎么传到后台？说一下思路，先把页面拼接成字符串（包括样式）。然后创建iframe,
虚拟机里面linux设置固定IP，访问外网
qq_35807697的博客
05-13 671
java技术分享 public int getNiticeListNum(HashMap inputData) { int count = 0; DataAccessUtil qu = new DataAccessUtil(); StringBuffer sql = new StringBuffer(""); sql.appen
写一个sql语句，查询工资第二高的人，如果不存在返回null
最新发布
06-11
以下是一个查询工资第二高的人的 SQL 语句示例，如果不存在则返回 NULL： ``` SELECT name, salary FROM employee WHERE salary = ( SELECT MAX(salary) FROM employee WHERE salary ( SELECT MAX(salary) ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值