2020-09-03 SQL 日记

开个帖子记录一下目前写到现在觉得最恶心的SQL，以后遇到更恶心的来更新一下

Oracle，8表关联，各种信息都是以Tag的形式记录，比如标识这个项目属于某部门，则在Tag表中创建一个名为XXXX的部门，在Tag_Link中创建 Tag和表的关联关系。且由于项目在不同的部门拥有不用的名字，还需建立一张项目映射表，去映射不同的部门之间的项目关系。可能A部门的项目A和B部门的项目B是一个项目。
此外，真实的项目还有收入类别等情况，仍需要建立关联表和体现项目和收入类别表之间的关系。

8表关联

多次用到了多表连接以及 列转行

@Query(value="select bumenPrjCode   as  \"prjCode\",\n"+
            "       bumenPrjName        as  \"prjName_CN\",\n" +
            "       BUSSINESS_MGR_CODE  as  \"businessMgrCode\",\n"+
            "       businessName        as  \"businessMgrName\",\n" +
            "       businessTypeCode    as  \"businessTypeCode\",\n" +
            "       businessType        as  \"businessTypeName\",\n" +
            "       TECHNICAL_MGR_CODE  as  \"technicalMgrCode\",\n"+
            "       technicalName       as  \"technicalMgrName\",\n" +
            "       developmentCode     as  \"developmentTypeCode\",\n" +
            "       developmentType     as  \"developmentTypeName\",\n"+
            "       customerTypeCode    as  \"customerTypeCode\",\n"+
            "       customerType        as  \"customerTypeName\",\n"+
            "       TO_CHAR(APPROVED_TIME, 'yyyy-mm-dd') as \"approvedTime\",\n" +
            "       TO_CHAR(OFFLINE_TIME, 'yyyy-mm-dd')  as \"offlineTime\",\n" +
            "       TO_CHAR(ONLINE_TIME, 'yyyy-mm-dd')   as \"onlineTime\",\n" +
            "       caiwuPrjName        as  \"financePrjName\",\n"+
            "       caiwuPrjCode        as  \"financePrjCode\",\n"+
            "       to_char(WM_CONCAT(PRJ_TYPE_CODE)) as \"financeprjTypeCode\",\n"+
            "       to_char(WM_CONCAT(INCOME_TYPE_CODE)) as \"financeincomeTypeCode\",\n" +
            "       to_char(WM_CONCAT(INCOME_TYPE_NAME)) as \"financeincomeTypeName\"\n" +
            "from (select bumenPrjCode,bumenPrjName,caiwuPrjCode,caiwuPrjName,INCOME_TYPE_CODE,INCOME_TYPE_NAME,PRJ_TYPE_CODE,BUSSINESS_MGR_CODE,businessName,TECHNICAL_MGR_CODE,technicalName,\n" +
            "                                                                                              (\n" +
            "                                                                                                  select t2.TAG_CODE\n" +
            "                                                                                                  from CA_TAG_LINK tl2\n" +
            "                                                                                                           join CA_TAG t2 on t2.TAG_CODE = tl2.TAG_CODE\n" +
            "                                                                                                  where tl2.ENTITY_CODE = bumenPrjCode\n" +
            "                                                                                                    and t2.TAG_TYPE_CODE = 'developmentType'\n" +
            "                                                                                              ) as developmentCode,\n" +
            "                                                                                              (\n" +
            "                                                                                                  select t2.TAG_NAME\n" +
            "                                                                                                  from CA_TAG_LINK tl2\n" +
            "                                                                                                           join CA_TAG t2 on t2.TAG_CODE = tl2.TAG_CODE\n" +
            "                                                                                                  where tl2.ENTITY_CODE = bumenPrjCode\n" +
            "                                                                                                    and t2.TAG_TYPE_CODE = 'developmentType'\n" +
            "                                                                                              ) as developmentType,\n" +
            "                                                                                              (\n" +
            "                                                                                                  select t3.TAG_Code\n" +
            "                                                                                                  from CA_TAG_LINK tl3\n" +
            "                                                                                                           join CA_TAG t3 on t3.TAG_CODE = tl3.TAG_CODE\n" +
            "                                                                                                  where tl3.ENTITY_CODE = bumenPrjCode\n" +
            "                                                                                                    and t3.TAG_TYPE_CODE = 'customerType'\n" +
            "                                                                                              ) as customerTypeCode,\n" +
            "                                                                                              (\n" +
            "                                                                                                  select t3.TAG_NAME\n" +
            "                                                                                                  from CA_TAG_LINK tl3\n" +
            "                                                                                                           join CA_TAG t3 on t3.TAG_CODE = tl3.TAG_CODE\n" +
            "                                                                                                  where tl3.ENTITY_CODE = bumenPrjCode\n" +
            "                                                                                                    and t3.TAG_TYPE_CODE = 'customerType'\n" +
            "                                                                                              ) as customerType,\n" +
            "                                                                                              (\n" +
            "                                                                                                  select t4.TAG_CODE\n" +
            "                                                                                                  from CA_TAG_LINK tl4\n" +
            "                                                                                                           join CA_TAG t4 on t4.TAG_CODE = tl4.TAG_CODE\n" +
            "                                                                                                  where tl4.ENTITY_CODE = bumenPrjCode\n" +
            "                                                                                                    and t4.TAG_TYPE_CODE = 'businessType'\n" +
            "                                                                                              ) as businessTypeCode,\n" +
            "                                                                                              (\n" +
            "                                                                                                  select t4.TAG_NAME\n" +
            "                                                                                                  from CA_TAG_LINK tl4\n" +
            "                                                                                                           join CA_TAG t4 on t4.TAG_CODE = tl4.TAG_CODE\n" +
            "                                                                                                  where tl4.ENTITY_CODE = bumenPrjCode\n" +
            "                                                                                                    and t4.TAG_TYPE_CODE = 'businessType'\n" +
            "                                                                                              ) as businessType,\n" +
            "                                                                                                ONLINE_TIME,\n" +
            "                                                                                                OFFLINE_TIME,\n" +
            "                                                                                                APPROVED_TIME\n" +
            "                                                                                       from (select EMPLOYEE_NAME as technicalName,businessName,bumenPrjCode,bumenPrjName,caiwuPrjCode,caiwuPrjName,INCOME_TYPE_CODE,INCOME_TYPE_NAME,PRJ_TYPE_CODE,BUSSINESS_MGR_CODE,TECHNICAL_MGR_CODE,ONLINE_TIME,OFFLINE_TIME,APPROVED_TIME from\n" +
            "                                                                                           (select EMPLOYEE_NAME as businessName,bumenPrjCode,bumenPrjName,caiwuPrjCode,caiwuPrjName,INCOME_TYPE_CODE,INCOME_TYPE_NAME,PRJ_TYPE_CODE,BUSSINESS_MGR_CODE,TECHNICAL_MGR_CODE,ONLINE_TIME,OFFLINE_TIME,APPROVED_TIME from\n" +
            "                                                                                               (select prj1 as bumenPrjCode,PRJ_NAME_CN as bumenPrjName,PRJ_CODE as caiwuPrjCode,PRJ_NAME as caiwuPrjName,CFIT.INCOME_TYPE_CODE,INCOME_TYPE_NAME,PRJ_TYPE_CODE,BUSSINESS_MGR_CODE,TECHNICAL_MGR_CODE,ONLINE_TIME,OFFLINE_TIME,APPROVED_TIME\n" +
            "                                                                                                from CA_FINANCE_INCOME_TYPE CFIT\n" +
            "                                                                                                         join (select INCOME_TYPE_CODE,CFPL.PRJ_CODE,PRJ_NAME,prj1,PRJ_NAME_CN,BUSSINESS_MGR_CODE,TECHNICAL_MGR_CODE,ONLINE_TIME,OFFLINE_TIME,APPROVED_TIME\n" +
            "                                                                                                               from  CA_FINANCE_PRJ_LINK CFPL\n" +
            "                                                                                                                         join (select CFPI.PRJ_CODE prj2,PRJ_NAME,DPI.PRJ_CODE as prj1,DPI.PRJ_NAME_CN,BUSSINESS_MGR_CODE,TECHNICAL_MGR_CODE,ONLINE_TIME,OFFLINE_TIME,APPROVED_TIME\n" +
            "                                                                                                                               from DEPT_PRJ_INFO DPI\n" +
            "                                                                                                                                        join CA_FINANCE_PRJ_INFO CFPI\n" +
            "                                                                                                                                             on CFPI.PRJ_CODE = DPI.FINANCE_PRJ_CODE) TEMP\n" +
            "                                                                                                                              on TEMP.prj2 = CFPL.PRJ_CODE) temp2\n" +
            "                                                                                                              on temp2.INCOME_TYPE_CODE = CFIT.INCOME_TYPE_CODE) temp4\n" +
            "                                                                                                   join CA_EMPLOYEE on CA_EMPLOYEE.EMPLOYEE_CODE=temp4.BUSSINESS_MGR_CODE) temp5\n" +
            "                                                                                               join CA_EMPLOYEE on temp5.TECHNICAL_MGR_CODE=CA_EMPLOYEE.EMPLOYEE_CODE\n" +
            "                                                                                            )) t\n" +
            "group by bumenPrjCode,bumenPrjName,caiwuPrjCode,caiwuPrjName,BUSSINESS_MGR_CODE,businessName,TECHNICAL_MGR_CODE,technicalName,ONLINE_TIME,OFFLINE_TIME,APPROVED_TIME,developmentCode,developmentType,customerTypeCode,customerType,businessTypeCode,businessType",nativeQuery = true)
    List<Map> getPrjInfo();

这辈子都不想再写SQL了