我有以下由某人编写的查询(数据库Microsoft SQL Server 2017(。我无法访问数据库来创建索引等，所以我唯一能做的就是尽可能高效地优化下面的查询。
我在这里的查询是，
我可以像在以下查询中使用的那样在内部查询中使用 Case 和 WHEN 吗？此外，我目前正在尝试使用内部连接删除嵌套查询。
对于以下查询，任何其他想法将不胜感激。我不要求给出最终优化的查询，而是询问特定于以下查询的建议。
WITH ChildPremiumCTE (COVER_PREMIUM_SET_ID,POLICY_ID, ADDRESS_ID, STATUS_CODE,COVER_PREMIUM_MODEL_ID, SUB_PRODUCT_REF
, COVER_PREMIUM_MODEL_REF, DESCRIPTION, PREMIUM_LEVEL_CODE
, PREMIUM_PAYABLE, PREMIUM_DUE, COMMISSION_GST, COMMISSION, COMMISSION_TOTAL,TERRORISM_GST
, TERRORISM_RI, STAMP_DUTY, GST, FSL
, MODIFIED_PREMIUM, TECHNICAL_PREMIUM, BASE_PREMIUM
, PREMIUM_PAYABLE_TERM, PREMIUM_DUE_TERM, COMMISSION_GST_TERM
, COMMISSION_TERM, COMMISSION_TOTAL_TERM, TERRORISM_GST_TERM
, TERRORISM_RI_TERM, STAMP_DUTY_TERM, GST_TERM, FSL_TERM
, MODIFIED_PREMIUM_TERM, TECHNICAL_PREMIUM_TERM, BASE_PREMIUM_TERM
, PREMIUM_PAYABLE_ANNUALISED, PREMIUM_DUE_ANNUALISED, COMMISSION_GST_ANNUALISED
, COMMISSION_ANNUALISED, COMMISSION_TOTAL_ANNUALISED, TERRORISM_GST_ANNUALISED
, TERRORISM_RI_ANNUALISED, STAMP_DUTY_ANNUALISED, GST_ANNUALISED
, FSL_ANNUALISED, MODIFIED_PREMIUM_ANNUALISED, TECHNICAL_PREMIUM_ANNUALISED
, BASE_PREMIUM_ANNUALISED)
AS (SELECT CPP.COVER_PREMIUM_SET_ID
, CPS.POLICY_ID
, CASE WHEN CPS.PREMIUM_LEVEL_CODE = 'PREMITEM' THEN SS.ADDRESS_ID ELSE AD.ADDRESS_ID END ADDRESS_ID
, SS.STATUS_CODE
, COALESCE(CPM.COVER_PREMIUM_MODEL_ID, 0) COVER_PREMIUM_MODEL_ID
, CASE WHEN CPS.PREMIUM_LEVEL_CODE = 'POLSUM' THEN 'POLICY_SUMMARY'
ELSE CASE WHEN CPS.PREMIUM_LEVEL_CODE = 'SITSUM' THEN 'SITUATION_SUMMARY' ELSE CPS.SUB_PRODUCT_REF END END SUB_PRODUCT_REF
, CPS.COVER_PREMIUM_MODEL_REF
, CPS.DESCRIPTION
, CPS.PREMIUM_LEVEL_CODE
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'PREMIUM_PAYABLE' THEN CPP.TRANSACTION_AMOUNT END) PREMIUM_PAYABLE
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'PREMIUM_DUE' THEN CPP.TRANSACTION_AMOUNT END) PREMIUM_DUE
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'COMMISSION_GST' THEN CPP.TRANSACTION_AMOUNT END) COMMISSION_GST
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'COMMISSION' THEN CPP.TRANSACTION_AMOUNT END) COMMISSION
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'COMMISSION_TOTAL' THEN CPP.TRANSACTION_AMOUNT END) COMMISSION_TOTAL
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'TERRORISM_GST' THEN CPP.TRANSACTION_AMOUNT END) TERRORISM_GST
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'TERRORISM_RI' THEN CPP.TRANSACTION_AMOUNT END) TERRORISM_RI
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'STAMP_DUTY' THEN CPP.TRANSACTION_AMOUNT END) STAMP_DUTY
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'GST' THEN CPP.TRANSACTION_AMOUNT END) GST
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'FSL' THEN CPP.TRANSACTION_AMOUNT END) FSL
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'MODIFIED_PREMIUM' THEN CPP.TRANSACTION_AMOUNT END) MODIFIED_PREMIUM
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'TECHNICAL_PREMIUM' THEN CPP.TRANSACTION_AMOUNT END) TECHNICAL_PREMIUM
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'BASE_PREMIUM' THEN CPP.TRANSACTION_AMOUNT END) BASE_PREMIUM
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'PREMIUM_PAYABLE' THEN CPP.TERM_AMOUNT END) PREMIUM_PAYABLE_TERM
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'PREMIUM_DUE' THEN CPP.TERM_AMOUNT END) PREMIUM_DUE_TERM
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'COMMISSION_GST' THEN CPP.TERM_AMOUNT END) COMMISSION_GST_TERM
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'COMMISSION' THEN CPP.TERM_AMOUNT END) COMMISSION_TERM
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'COMMISSION_TOTAL' THEN CPP.TERM_AMOUNT END) COMMISSION_TOTAL_TERM
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'TERRORISM_GST' THEN CPP.TERM_AMOUNT END) TERRORISM_GST_TERM
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'TERRORISM_RI' THEN CPP.TERM_AMOUNT END) TERRORISM_RI_TERM
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'STAMP_DUTY' THEN CPP.TERM_AMOUNT END) STAMP_DUTY_TERM
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'GST' THEN CPP.TERM_AMOUNT END) GST_TERM
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'FSL' THEN CPP.TERM_AMOUNT END) FSL_TERM
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'MODIFIED_PREMIUM' THEN CPP.TERM_AMOUNT END) MODIFIED_PREMIUM_TERM
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'TECHNICAL_PREMIUM' THEN CPP.TERM_AMOUNT END) TECHNICAL_PREMIUM_TERM
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'BASE_PREMIUM' THEN CPP.TERM_AMOUNT END) BASE_PREMIUM_TERM
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'PREMIUM_PAYABLE' THEN CPP.FUTURE_ANNUALISED_AMOUNT END) PREMIUM_PAYABLE_ANNUALISED
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'PREMIUM_DUE' THEN CPP.FUTURE_ANNUALISED_AMOUNT END) PREMIUM_DUE_ANNUALISED
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'COMMISSION_GST' THEN CPP.FUTURE_ANNUALISED_AMOUNT END) COMMISSION_GST_ANNUALISED
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'COMMISSION' THEN CPP.FUTURE_ANNUALISED_AMOUNT END) COMMISSION_ANNUALISED
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'COMMISSION_TOTAL' THEN CPP.FUTURE_ANNUALISED_AMOUNT END) COMMISSION_TOTAL_ANNUALISED
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'TERRORISM_GST' THEN CPP.FUTURE_ANNUALISED_AMOUNT END) TERRORISM_GST_ANNUALISED
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'TERRORISM_RI' THEN CPP.FUTURE_ANNUALISED_AMOUNT END) TERRORISM_RI_ANNUALISED
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'STAMP_DUTY' THEN CPP.FUTURE_ANNUALISED_AMOUNT END) STAMP_DUTY_ANNUALISED
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'GST' THEN CPP.FUTURE_ANNUALISED_AMOUNT END) GST_ANNUALISED
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'FSL' THEN CPP.FUTURE_ANNUALISED_AMOUNT END) FSL_ANNUALISED
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'MODIFIED_PREMIUM' THEN CPP.FUTURE_ANNUALISED_AMOUNT END) MODIFIED_PREMIUM_ANNUALISED
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'TECHNICAL_PREMIUM' THEN CPP.FUTURE_ANNUALISED_AMOUNT END) TECHNICAL_PREMIUM_ANNUALISED
, MAX(CASE WHEN CPP.PREMIUM_MODEL_PART_REF = 'BASE_PREMIUM' THEN CPP.FUTURE_ANNUALISED_AMOUNT END) BASE_PREMIUM_ANNUALISED
FROM COVER_PREMIUM_SET As CPS
LEFT JOIN SITUATION_SECTION As SS ON CPS.POLICY_ID = SS.POLICY_ID
AND CPS.SITUATION_SECTION_REF = SS.SITUATION_SECTION_REF
LEFT JOIN COVER_PREMIUM_PART As CPP ON CPS.COVER_PREMIUM_SET_ID = CPP.COVER_PREMIUM_SET_ID
LEFT JOIN ADDRESS As AD ON CPS.POLICY_ID = AD.POLICY_ID
AND AD.ADDRESS_REF = CPS.ENTITY_INSTANCE_REF
AND CPS.PREMIUM_LEVEL_CODE IN ('SITSUM', 'SUBPRODSUM')
LEFT JOIN COVER_PREMIUM_MODEL As CPM ON CPS.COVER_PREMIUM_MODEL_REF = CPM.COVER_PREMIUM_MODEL_REF
GROUP BY CPP.COVER_PREMIUM_SET_ID,
CPS.POLICY_ID,
CPS.DESCRIPTION,
CPS.PREMIUM_LEVEL_CODE,
CPS.COVER_PREMIUM_MODEL_REF,
CPS.SUB_PRODUCT_REF,
CPM.COVER_PREMIUM_MODEL_ID,
SS.SITUATION_SECTION_ID,
SS.ADDRESS_ID,
SS.STATUS_CODE,
CPS.ENTITY_INSTANCE_REF,
AD.ADDRESS_ID )
select * from (SELECT
0 AS COVER_PREMIUM_SET_ID
, a1.POLICY_ID
, a1.ADDRESS_ID
, a1.STATUS_CODE
, CPM_CHILD.PARENT_ID AS COVER_PREMIUM_MODEL_ID
, CASE WHEN CPM.PREMIUM_LEVEL_CODE = 'POLSUM' THEN 'POLICY_SUMMARY'
ELSE CASE WHEN CPM.PREMIUM_LEVEL_CODE = 'SITSUM' THEN 'SITUATION_SUMMARY' ELSE a1.SUB_PRODUCT_REF END END SUB_PRODUCT_REF
, CPM.COVER_PREMIUM_MODEL_REF
, CPM.NAME AS Description
, CPM.PREMIUM_LEVEL_CODE AS PREMIUM_LEVEL_CODE
, sum(a1.PREMIUM_PAYABLE) PREMIUM_PAYABLE
, sum(a1.PREMIUM_DUE) PREMIUM_DUE
, sum(a1.COMMISSION_GST) COMMISSION_GST
, sum(a1.COMMISSION) COMMISSION
, sum(a1.COMMISSION_TOTAL) COMMISSION_TOTAL,sum(a1.TERRORISM_GST) TERRORISM_GST
, sum(a1.TERRORISM_RI) TERRORISM_RI, sum(a1.STAMP_DUTY) STAMP_DUTY,sum(a1.GST ) GST, sum(a1.FSL ) FSL
, sum(a1.MODIFIED_PREMIUM ) MODIFIED_PREMIUM, sum(a1.TECHNICAL_PREMIUM ) TECHNICAL_PREMIUM, sum(a1.BASE_PREMIUM ) BASE_PREMIUM
, sum(a1.PREMIUM_PAYABLE_TERM) PREMIUM_PAYABLE_TERM, sum(a1.PREMIUM_DUE_TERM ) PREMIUM_DUE_TERM
, sum(a1.COMMISSION_GST_TERM ) COMMISSION_GST_TERM, sum(a1.COMMISSION_TERM ) COMMISSION_TERM
, sum(a1.COMMISSION_TOTAL_TERM ) COMMISSION_TOTAL_TERM, sum(a1.TERRORISM_GST_TERM ) TERRORISM_GST_TERM
, sum(a1.TERRORISM_RI_TERM ) TERRORISM_RI_TERM, sum(a1.STAMP_DUTY_TERM ) STAMP_DUTY_TERM
, sum(a1.GST_TERM ) GST_TERM, sum(a1.FSL_TERM ) FSL_TERM, sum(a1.MODIFIED_PREMIUM_TERM ) MODIFIED_PREMIUM_TERM
, sum(a1.TECHNICAL_PREMIUM_TERM ) TECHNICAL_PREMIUM_TERM, sum(a1.BASE_PREMIUM_TERM ) BASE_PREMIUM_TERM
, sum(a1.PREMIUM_PAYABLE_ANNUALISED) PREMIUM_PAYABLE_ANNUALISED, sum(a1.PREMIUM_DUE_ANNUALISED ) PREMIUM_DUE_ANNUALISED
, sum(a1.COMMISSION_GST_ANNUALISED ) COMMISSION_GST_ANNUALISED, sum(a1. COMMISSION_ANNUALISED ) COMMISSION_ANNUALISED
, sum(a1. COMMISSION_TOTAL_ANNUALISED ) COMMISSION_TOTAL_ANNUALISED, sum(a1.TERRORISM_GST_ANNUALISED ) TERRORISM_GST_ANNUALISED
, sum(a1.TERRORISM_RI_ANNUALISED ) TERRORISM_RI_ANNUALISED, sum(a1.STAMP_DUTY_ANNUALISED ) STAMP_DUTY_ANNUALISED
, sum(a1.GST_ANNUALISED ) GST_ANNUALISED, sum(a1.FSL_ANNUALISED ) FSL_ANNUALISED
, sum(a1.MODIFIED_PREMIUM_ANNUALISED ) MODIFIED_PREMIUM_ANNUALISED
, sum(a1.TECHNICAL_PREMIUM_ANNUALISED ) TECHNICAL_PREMIUM_ANNUALISED
, sum(a1.BASE_PREMIUM_ANNUALISED) BASE_PREMIUM_ANNUALISED
FROM COVER_PREMIUM_MODEL CPM,
COVER_PREMIUM_MODEL CPM_CHILD,
ChildPremiumCTE a1
WHERE
CPM.PREMIUM_LEVEL_CODE = 'PREMRLUP'
AND CPM_CHILD.PARENT_ID = CPM.COVER_PREMIUM_MODEL_ID
AND CPM_CHILD.COVER_PREMIUM_MODEL_REF = a1.COVER_PREMIUM_MODEL_REF
GROUP BY
a1.POLICY_ID,
a1.ADDRESS_ID,
CPM_CHILD.PARENT_ID,
CPM.NAME,
CPM.PREMIUM_LEVEL_CODE,
a1.SUB_PRODUCT_REF,
CPM.COVER_PREMIUM_MODEL_REF,
a1.STATUS_CODE
UNION ALL
select * from ChildPremiumCTE) CoverPremium where CoverPremium.POLICY_ID = 77780029
到目前为止，我改进的是，
- 使工会联合到工会
- 尝试将子查询转换为 where 子句
- 内置函数，如 MAX、SUM 以喜欢字符串文字
第一个建议与@LukStorms相同。也始终使用表的别名并在列中为它们添加前缀，以便非常易于理解和调试。
我可以像在以下查询中使用的那样在内部查询中使用 Case 和 WHEN 吗？
一切都有代价。在这种情况下，成本似乎可以忽略不计。
对于以下查询，任何其他想法将不胜感激。我不是 要求给出最终优化查询，但要求具体建议 到下面的查询。
GROUP BY"全部"列总是坏主意。首先使用 CTE 或 #Temp 表 仅使用"需要表"和"键上的分组依据"列编写"分组依据"查询。
假设您还需要其他一些不属于聚合或分组依据的列，那么没问题，请不要尝试在此处使用该列。 在主查询中联接同一表以获取不属于聚合的列。
然后在主查询中再次将该 CTE 或临时表与其他表联接。
这就是您应该如何设计查询。
PREMIUM_MODEL_PART_REF and TRANSACTION_AMOUNT belong to which table ?
表COVER_PREMIUM_MODEL在查询中使用 3 次。将COVER_PREMIUM_MODEL所需的数据放在#temp table上。所有三个连接都必须有一些共同的结果。
摘录
select * from (Select * blah blah
UNION ALL
select * from ChildPremiumCTE) a2 where a2.POLICY_ID = 77780029
为什么最后使用where POLICY_ID = 77780029？ 这样，首先将在内部查询中处理如此多的行，然后最终减少到POLICY_ID = 77780029。
那么为什么不首先在内部查询中使用POLICY_ID = 77780029呢？
内置函数，如 MAX、SUM 以喜欢字符串文字
这取决于它是什么类型的UDF以及您在其中写了什么。 简而言之，UDF大多数时候都是出了名的糟糕表现。
似乎不需要MAX, SUMUDF。