多次连接同一张表

您正在使用master_attribute_id表上的所有子查询来杀死自己。我已经更改了关于做JOIN的查询。从原始的“ A”别名开始，然后在相同的“ A.TENANT_ID”（不是固定值）上对每个属性表进行JOIN，但每个实例还另外加上AND ... LABEL ='whatever'。因此，每个联接都会根据相同的租户和标签进入自定义属性。现在，从每个目录中，通过MAP_ID连接到属性表。因此，根据需要，我的别名与“ CAV”（custom_Addtribute_Values）表以及A，B，C，D相关联。因此，您只需要最外面的WHERE子句中的实际租户ID。

现在，为了帮助确保查询的优化（MST_TENANT_CUSTOM_ATTRIBUTES表），我将覆盖一个索引（tenant_id，label，map_id）。对于Custom_Attributes_Values表，我将在（map_id，value）上有一个索引

SELECT 
      A.PROJECT_ID,
      A.PROJECT_BASIC_INFORMATION_ID,
      CAV_A.VALUE AS BU_START_DATE,
      CAV_B.VALUE AS BU_DURATION,
      CAV_C.VALUE AS BU_AMOUNT,
      E.VALUE AS PLUG 
   FROM 
      PROJECT_BASIC_INFORMATION A
         JOIN MST_TENANT_CUSTOM_ATTRIBUTES MT_A
            ON A.TENANT_ID = MT_A.TENANT_ID
            AND MT_A.LABEL = 'Budget Project Savings Start Date' 
            JOIN CUSTOM_ATTRIBUTES_VALUES A
               ON MT_A.MAP_ID = CAV_A.MAP_ID

         JOIN MST_TENANT_CUSTOM_ATTRIBUTES MT_B
            ON A.TENANT_ID = MT_B.TENANT_ID
            AND MA_B.LABEL = 'Budget Savings Duration' 
            JOIN CUSTOM_ATTRIBUTES_VALUES B
               ON MT_B.MAP_ID = CAV_B.MAP_ID

         JOIN MST_TENANT_CUSTOM_ATTRIBUTES MT_C
            ON A.TENANT_ID = MT_C.TENANT_ID
            AND MA_C.LABEL = 'Budget Annualized Savings'
            JOIN CUSTOM_ATTRIBUTES_VALUES C
               ON MT_C.MAP_ID = CAV_C.MAP_ID

         JOIN MST_TENANT_CUSTOM_ATTRIBUTES MT_D
            ON A.TENANT_ID = MT_D.TENANT_ID
            AND MA_D.LABEL = 'Plug' 
            JOIN CUSTOM_ATTRIBUTES_VALUES D
               ON MT_D.MAP_ID = CAV_D.MAP_ID
   WHERE 
      A.TENANT_ID = 100
   GROUP BY 
      A.PROJECT_BASIC_INFORMATION_ID 
   ORDER BY 
      A.PROJECT_ID