sql-server - 我可以将 select 语句中的 select 转换为自连接吗？

Question

这可能是个愚蠢的问题，但我想不通我有这样的查询:

select TERM_SID
,max(PUBLICDATA) as PublicData
,max(PUBLIC_AGGREGATE) as Public_AGGregate
,max(INTERNAL) as Internal
,max(INTERNAL_AGGREGATE) as internal_Aggregate
,max(LIMITED) as Limited
,max(RESTRICTED) as Restricted
from
(
      SELECT  TERM_SID,
      CASE WHEN d2.DATA_CLASSIFICATION_DESC='Public data' THEN 'Y' ELSE 'N' END AS 'PUBLICDATA',
      CASE WHEN d2.DATA_CLASSIFICATION_DESC='Public data in aggregate' THEN 'Y'  ELSE 'N' END as 'PUBLIC_AGGREGATE',
      CASE WHEN d2.DATA_CLASSIFICATION_DESC='Internal data' THEN 'Y'  ELSE 'N' END as 'INTERNAL',
      CASE WHEN d2.DATA_CLASSIFICATION_DESC='Internal data in aggregate' THEN 'Y'  ELSE 'N' END as 'INTERNAL_AGGREGATE',
      CASE WHEN d2.DATA_CLASSIFICATION_DESC='Limited data' THEN 'Y'  ELSE 'N' END as 'LIMITED',
      CASE WHEN d2.DATA_CLASSIFICATION_DESC='Restricted data' THEN 'Y'  ELSE 'N' END as 'RESTRICTED'
      FROM [UDW_DATA_DICTIONARY].[dbo].[DATA_CLASSIFICATION] d2
      JOIN [UDW_DATA_DICTIONARY].[dbo].[DATA_CLASSIFICATION_MAPPING] dm2 ON dm2.DATA_CLASSIFICATION_SID=d2.DATA_CLASSIFICATION_SID
) mat
group by Term_SID

我尝试过转换它，但我一直被内部选择中的连接绊倒，有没有办法用连接重建它以提高性能。

Answer 1

使用 Common Table Expression 将其转换为派生表(CTE)，您可以像引用任何其他表一样引用它:

with mat as
(
      SELECT  TERM_SID
             ,CASE WHEN d2.DATA_CLASSIFICATION_DESC = 'Public data' THEN 'Y' ELSE 'N' END AS PUBLICDATA
             ,CASE WHEN d2.DATA_CLASSIFICATION_DESC = 'Public data in aggregate' THEN 'Y'  ELSE 'N' END as PUBLIC_AGGREGATE
             ,CASE WHEN d2.DATA_CLASSIFICATION_DESC = 'Internal data' THEN 'Y'  ELSE 'N' END as INTERNAL
             ,CASE WHEN d2.DATA_CLASSIFICATION_DESC = 'Internal data in aggregate' THEN 'Y'  ELSE 'N' END as INTERNAL_AGGREGATE
             ,CASE WHEN d2.DATA_CLASSIFICATION_DESC = 'Limited data' THEN 'Y'  ELSE 'N' END as LIMITED
             ,CASE WHEN d2.DATA_CLASSIFICATION_DESC = 'Restricted data' THEN 'Y'  ELSE 'N' END as RESTRICTED
      FROM [UDW_DATA_DICTIONARY].[dbo].[DATA_CLASSIFICATION] d2
          JOIN [UDW_DATA_DICTIONARY].[dbo].[DATA_CLASSIFICATION_MAPPING] dm2
              ON dm2.DATA_CLASSIFICATION_SID = d2.DATA_CLASSIFICATION_SID
)
select m1.TERM_SID
      ,m1.max(PUBLICDATA) as PublicData
      ,m1.max(PUBLIC_AGGREGATE) as Public_AGGregate
      ,m1.max(INTERNAL) as Internal
      ,m1.max(INTERNAL_AGGREGATE) as internal_Aggregate
      ,m1.max(LIMITED) as Limited
      ,m1.max(RESTRICTED) as Restricted

      ,m2.count(1) as mat2count
from mat m1
    left join mat m2
        on(m1.TERM_SID = m2.TERM_SID)
group by Term_SID