开个帖子记录一下目前写到现在觉得最恶心的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了
