带内连接的枢轴表(或其他解决方案)

Question

我有一个疑问：

SELECT  DISTINCT  
    a.max_TEA_InicioTarefa, 
    analista,
    ETS.ETS_Sigla, 
    ATC.ATC_Id, 
    ATC.ATC_Sigla, 
    PAT.PAT_Sigla, 
    a.SRV_Id,
    ContratoComunicado.CCM_Id,
    ContratoComunicado.CCM_Docto, 
    ContratoComunicado.CCM_Emissao,
    ComunicadoTipo.CMT_Descr,
    TarefaEtapaAreaTecnica_1.TEA_Revisao, 
    TarefaEtapaAreaTecnica_1.ETS_Id, 
    TarefaEtapaAreaTecnica_1.TEA_FimTarefa, 
    TarefaEtapaAreaTecnica_1.PAT_Id 
FROM       
    dbo.Tarefa AS Tarefa_1
INNER JOIN
    (
        SELECT 
            MAX(dbo.TarefaEtapaAreaTecnica.TEA_InicioTarefa) 
                AS max_TEA_InicioTarefa, 
            dbo.Pessoa.PFJ_Descri as analista,
            dbo.AreaTecnica.ATC_Id, 
            dbo.Tarefa.SRV_Id
        FROM  
            dbo.TarefaEtapaAreaTecnica 
            LEFT OUTER JOIN dbo.Tarefa 
                ON dbo.TarefaEtapaAreaTecnica.TRF_Id = 
                dbo.Tarefa.TRF_Id
            LEFT OUTER JOIN dbo.AreaTecnica 
                ON dbo.TarefaEtapaAreaTecnica.ATC_Id = 
                dbo.AreaTecnica.ATC_Id 
            LEFT OUTER JOIN dbo.Pessoa 
                ON dbo.Pessoa.PFJ_Id = 
                dbo.TarefaEtapaAreaTecnica.PFJ_Id_Analista
        GROUP BY 
            dbo.AreaTecnica.ATC_Id, 
            dbo.Tarefa.SRV_Id, 
            dbo.Pessoa.PFJ_Descri
    ) AS a 
        ON Tarefa_1.SRV_Id = a.SRV_Id 
INNER JOIN dbo.TarefaEtapaAreaTecnica AS TarefaEtapaAreaTecnica_1 
    ON Tarefa_1.TRF_Id = TarefaEtapaAreaTecnica_1.TRF_Id AND 
    a.ATC_Id = TarefaEtapaAreaTecnica_1.ATC_Id AND
    a.max_TEA_InicioTarefa = TarefaEtapaAreaTecnica_1.TEA_InicioTarefa
LEFT JOIN AreaTecnica ATC ON 
    TarefaEtapaAreaTecnica_1.ATC_Id = ATC.ATC_Id
LEFT JOIN Etapa ETS ON 
    TarefaEtapaAreaTecnica_1.ETS_Id = ETS.ETS_Id
LEFT JOIN ParecerTipo PAT ON 
    TarefaEtapaAreaTecnica_1.PAT_Id = PAT.PAT_Id 
LEFT JOIN dbo.Servico ON 
    dbo.Servico.SRV_Id = Tarefa_1.SRV_Id
LEFT JOIN dbo.Contrato ON 
    dbo.Contrato.CNT_Id = Servico.CNT_Id
LEFT JOIN dbo.ContratoComunicado ON 
    dbo.Contrato.CNT_Id = ContratoComunicado.CNT_Id 
LEFT JOIN dbo.ComunicadoTipo ON 
    dbo.ComunicadoTipo.CMT_Id = ContratoComunicado.CMT_Id  

因此，我有以下几行：

​

[1]：http://i.stack.imgur.com/2btx9.jpg

就像。第6行和第7行基本相同，但CCM_Id、CCM_Docto、CCM_Emissao (来自表ContratoComunicado)和CMT_Descr (来自表ComunicadoTipo)则不然，我需要将CMT_Descr (来自表ComunicadoTipo)作为表ContratoComunicado中每个CMT_Id的列，因此我将有：

Pré-Advertencia日期x Pré-Advertencia Docto \1 a广告日期等。

2014-05-08 \x{e76f} 46/00365/14

通过这样做，我将保证不会有平等的线条。我在这方面有麻烦，有人能帮我吗？谢谢

Answer 1

做完后，我设法用旧的方式做：

 SELECT DISTINCT   
    SRV.INT_Id, DOCTO.CNT_Id,
     a.max_TEA_InicioTarefa, 
           analista,
           ETS.ETS_Sigla, 
           ATC.ATC_Id, 
           ATC.ATC_Sigla, 
           PAT.PAT_Sigla, 
           a.SRV_Id,
           TarefaEtapaAreaTecnica_1.TEA_Revisao, 
           TarefaEtapaAreaTecnica_1.ETS_Id, 
           TarefaEtapaAreaTecnica_1.TEA_FimTarefa, 
           TarefaEtapaAreaTecnica_1.PAT_Id, 
           DATAPRE, 
           DOCTOPRE, 
           DATA1A,
           DOCTO1A,
           DATA2A,
           DOCTO2A, 
           DATA3A,
           DOCTO3A, 
           DATAPA, 
           DOCTOPA, 
           DATARESC, 
           DOCTORESC
FROM       
    dbo.Tarefa AS Tarefa_1
INNER JOIN
    (
        SELECT 
            MAX(dbo.TarefaEtapaAreaTecnica.TEA_InicioTarefa) 
                AS max_TEA_InicioTarefa, 
            dbo.Pessoa.PFJ_Descri as analista,
            dbo.AreaTecnica.ATC_Id, 
            dbo.Tarefa.SRV_Id
        FROM  
            dbo.TarefaEtapaAreaTecnica 
            LEFT OUTER JOIN dbo.Tarefa 
                ON dbo.TarefaEtapaAreaTecnica.TRF_Id = 
                dbo.Tarefa.TRF_Id
            LEFT OUTER JOIN dbo.AreaTecnica 
                ON dbo.TarefaEtapaAreaTecnica.ATC_Id = 
                dbo.AreaTecnica.ATC_Id 
            LEFT OUTER JOIN dbo.Pessoa 
                ON dbo.Pessoa.PFJ_Id = 
                dbo.TarefaEtapaAreaTecnica.PFJ_Id_Analista
        GROUP BY 
            dbo.AreaTecnica.ATC_Id, 
            dbo.Tarefa.SRV_Id, 
            dbo.Pessoa.PFJ_Descri
    ) AS a 
        ON Tarefa_1.SRV_Id = a.SRV_Id 
INNER JOIN dbo.TarefaEtapaAreaTecnica AS TarefaEtapaAreaTecnica_1 
    ON Tarefa_1.TRF_Id = TarefaEtapaAreaTecnica_1.TRF_Id AND 
    a.ATC_Id = TarefaEtapaAreaTecnica_1.ATC_Id AND
    a.max_TEA_InicioTarefa = TarefaEtapaAreaTecnica_1.TEA_InicioTarefa
LEFT JOIN AreaTecnica ATC ON 
    TarefaEtapaAreaTecnica_1.ATC_Id = ATC.ATC_Id
LEFT JOIN Etapa ETS ON 
    TarefaEtapaAreaTecnica_1.ETS_Id = ETS.ETS_Id
LEFT JOIN ParecerTipo PAT ON 
    TarefaEtapaAreaTecnica_1.PAT_Id = PAT.PAT_Id 
LEFT JOIN dbo.Servico ON 
    dbo.Servico.SRV_Id = Tarefa_1.SRV_Id
LEFT JOIN dbo.Contrato ON 
    dbo.Contrato.CNT_Id = Servico.CNT_Id
LEFT JOIN dbo.ContratoComunicado ON 
    dbo.Contrato.CNT_Id = ContratoComunicado.CNT_Id 
LEFT JOIN dbo.ComunicadoTipo ON 
    dbo.ComunicadoTipo.CMT_Id = ContratoComunicado.CMT_Id  
LEFT JOIN Servico SRV ON 
    SRV.SRV_Id = Tarefa_1.SRV_Id
inner JOIN 
(
SELECT CC.CNT_Id,
         DATAPRE = MAX(case when CC.CMT_Id=5 then CC.CCM_Emissao end)
         , DOCTOPRE = MAX(case when CC.CMT_Id=5 then CC.CCM_Docto end)
         , DATA1A = MAX(case when CC.CMT_Id=2 then CC.CCM_Emissao end)
         , DOCTO1A = MAX(case when CC.CMT_Id=2 then CC.CCM_Docto end)
         , DATA2A = MAX(case when CC.CMT_Id=3 then CC.CCM_Emissao end)
         , DOCTO2A = MAX(case when CC.CMT_Id=3 then CC.CCM_Docto end)
         , DATA3A = MAX(case when CC.CMT_Id=7 then CC.CCM_Emissao end)
         , DOCTO3A = MAX(case when CC.CMT_Id=7 then CC.CCM_Docto end)
         , DATAPA = MAX(case when CC.CMT_Id=8 then CC.CCM_Emissao end)
         , DOCTOPA = MAX(case when CC.CMT_Id=8 then CC.CCM_Docto end)
         , DATARESC = MAX(case when CC.CMT_Id=4 then CC.CCM_Emissao end)
         , DOCTORESC = MAX(case when CC.CMT_Id=4 then CC.CCM_Docto end)
from ContratoComunicado AS CC  
GROUP BY CC.CNT_Id 
) AS DOCTO
ON DOCTO.CNT_Id = SRV.CNT_Id